CODEWITHSUNDEEP
sqlt-sql
Brad Boyce
Brad Boyce

Reputation: 1258

TSQL: How to use variable that could be null or int in a single select

My question is can I do the functions of these two selects in a single statement and get rid of the 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

Upvotes: 7

Views: 9602

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

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

Upvotes: 2

T.J. Crowder
T.J. Crowder

Reputation: 1074198

How 'bout this:

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 null case. If @recID is not null, the first part will match when appropriate (and the second part will be ignored). So both cases are covered.

Upvotes: 10

Related Questions