Geek asks and answers

SQL where clause with case argument

I have a problem with SQL, I have not yet found a solution, what I'm trying to do is where clause for a procedure in which the userID variable can contain either a valid user ID or -1 to indicate all users.

However, I am stuck in this part of the where clause:

AND usertable.userid = CASE WHEN @user = -1
THEN

ELSE
  @user
END

I am not sure how to handle -1 to indicate all users

thank

+1
source share
4 answers

Perhaps the following will work:

SELECT whatever
  FROM wherever
  WHERE something = somethingelse AND
        usertable.userid = CASE @user
                             WHEN -1 THEN usertable.userid
                             ELSE @user
                           END

Share and enjoy.

+5
source

you can change the offer in such a way as to return all users ...

AND ( @user = -1 
    OR usertable.userid = @user
)

There is no need for a case statement this way ...

+5
source

if...

-

if (@userId = -1)
    SELECT * FROM usertable
else
    SELECT * FROM usertable WHERE Id = @userId
+1
usertable.userid = COALESCE(NULLIF(@user, -1), usertable.userid)
+1

More articles:


All Articles