Geek Answers Handbook

TSQL: how to use a variable that can be null or int in one select

My question is: can I perform the functions of these two samples in one statement and get rid of IF?

DECLARE @recID INT; --Case 1 SET @recID = null; --Case 2 --SET @recID = 117; IF @recID is null BEGIN select * from myTable WHERE [myIDcolumn] is null -- works when recID is null END ELSE BEGIN select * from myTable WHERE [myIDcolumn] = @recID -- works when recID is number END
+7
source share
2 answers

How to do it:

 select * from myTable WHERE [myIDcolumn] = @recID or (@recID is null and [myIDcolumn] is null)

If @recID is null , the first part will never be true, but the second part will be if [myIDcolumn] is null , which covers the case of null . If @recID not null , the first part will match when necessary (and the second part will be ignored). Thus, both cases are covered.

+10
source

You can express the condition a little differently:

  • Either both @recID and [myIDcolumn] must be null , or

  • [myIDcolumn] must be equal to @recID

    select * from myTable WHERE ([myIDcolumn] is null AND @recID is null) OR [myIDcolumn] = @recID

+2
source

More articles:


All Articles