Saturday, May 9, 2015

Using Case Statement in Where clause

Recently I faced a situation where I need to decide by where clause value based on certain condition

so situation was some thing like this: To select some records for a userid and if userid passed is 0 then also we need to get records so my query was something like this:

select * from [user]
Where userId = @userId

Not if @userId is passed as 0 still I want the above query to get executed one way was to put if condition like

if @userId > 0 then
BEGIN
                 select * from [user]
END
ELSE
                  select * from [user]
                 Where userId = @userId

But I found a more smarter way ( which I think it is)

select * from [user]
Where userId = ( Case When @userId != 0 Then
                                         @userId
                              Else
                                        userId
                              End)

:) isnt't it cool :)





No comments:

Post a Comment