Select all data including null if the SQL variable is null

Question

I have to run a query in SQL Server 2012 to select data from the table. There is a variable @status. If this variable is null then all the data must be returned else if value

I have a sample query for this

SELECT * 
FROM StatusTable 
WHERE (Status = IIF(@status IS NULL, status, @status))

This query works fine when @status is not null but when it is null, it return only the rows where the status column is not null.

Sample table:

| id | status | Data |
|----|--------|------|
| 1  | null   | a    |
| 2  | ok     | b    |
| 3  | fail   | c    |
| 4  | fail   | d    |
| 5  | null   | e    |

when @status is "fail":

| id | status | Data |
|----|--------|------|
| 3  | fail   | c    |
| 4  | fail   | d    |

But when @status is null, it returns:

| id | status | Data |
|----|--------|------|
| 2  | ok     | b    |
| 3  | fail   | c    |
| 4  | fail   | d    |

Kindly help, Thanks

PS: in case of null status, I want the whole (sample) table to be returned

Gordon Linoff · Accepted Answer · 2017-01-06 13:06:27Z

5

Use or:

SELECT *
FROM StatusTable
WHERE (Status = @Status or @Status IS NULL);

answered Jan 6, 2017 at 13:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dai Over a year ago

I understand this style of query will result in suboptimal execution plans.

Vladimir Baranov Over a year ago

@Dai, if you add OPTION (RECOMPILE) (and you should add this option to this kind of query), then execution plan will always be optimal.

Vladimir Baranov Over a year ago

@RaduGheorghiu, with OPTION(RECOMPILE), if the value of the @Status variable is NULL, optimiser would remove the whole WHERE condition from the query. As if there was no WHERE at all. If @Status is not NULL, optimiser would look at available statistics for the specific given non-NULL value of the parameter and make a decision what is better - scan or seek.

Radu Gheorghiu Over a year ago

@VladimirBaranov Please excuse my rant, somehow I didn't see the @ symbol in front of the @Status IS NULL and confused it with Status IS NULL.

Rohit Raj Over a year ago

Hi, this query works perfectly however, I was looking for a query which is a bit faster, somewhat on the lines of CASE ..WHEN..THERE..END

Collectives™ on Stack Overflow

Select all data including null if the SQL variable is null

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related