confusing where clause in SQL Server
I want to have a simple condition in my where clause, on one of the columns like:
When @PostTypeid is null
then where should be PostTypeid in (4,5)
and if
@PostTypeid is not null
then where clause should be PostTypeId = @PostTypeId
I am trying to use case for this purpose but I think I am getting confused with the syntax. Here is what I have come up with so far:
(PostTypeid = case(@PostTypeId) when null then in (4,18) else @PostTypeId end)
The error is when I use in, I dont think that's a valid syntax. What is the correct way to do this? thanks. I Tried shifting the braces to several other places but in vain.
开发者_如何学PythonThanks
Something like:
WHERE
(@PostTypeID IS NULL AND PostTypeId IN (4, 5)) OR
(@PostTypeId IS NOT NULL AND PostTypeID = @PostTypeID)
perhaps?
You cant use = and in operators in one statement
EDIT:
(PostTypeid = case(@PostTypeId) when null then in (4,18) else @PostTypeId end)
if the @PostTypeId is null then it will goes like this.
(PostTypeid = in (4,8))
and this is syntax error.
case(@PostTypeID) when IS NULL then PostyTypeId in (4,18) else PostTypeId = PostTypeId
this case should return single result.
精彩评论