CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Rohit Raj
Rohit Raj

Reputation: 133

Select all data including null if the SQL variable is null

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

Upvotes: 1

Views: 3074

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use or:

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

Upvotes: 5

Related Questions