CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user1289451
user1289451

Reputation: 921

Is there a way to set the comparative in a WHERE clause to IS NULL or IS NOT NULL based on a variable

I'm no SQL expert and this has me stumped.

I have a stored procedure that I'm calling. I pass it a variable named @Requested which is used as a boolean.

What I'm trying:

SELECT 
    ID 
FROM 
    SomeTable
WHERE 
    Requested = CASE @Requested WHEN 0 THEN IS NULL WHEN 1 THEN IS NOT NULL END
;

I get errors in SQL Server Management Studio stating Incorrect syntax near 'IS'

The above code works if I do THEN 'some string', obviously.

I've done this as Requested CASE... and Requested = CASE

Essentially what I want is

Requested IS NULL

OR

Requested IS NOT NULL

based on what value is passed in as @Requested.

Any ideas?

Thanks

Upvotes: 3

Views: 104

Answers (2)

HABO
HABO

Reputation: 15852

Since T-SQL has limited support for boolean data you're stuck with something clumsy like:

case when @Requested = 0 then
  ( case when Requested is NULL then 1 else 0 end )
  else
  ( case when Requested is not NULL then 1 else 0 end )
  end = 1

Note that the result of the CASE is a value that needs to be used, e.g. compared to 1.

Alternatively, you could move up some of the decision making prior to the query:

declare @IfNull as Int = case when @Requested = 0 then 1 else 0 end;
declare @IfNotNull as Int = 1 - @IfNull;

select ...
  where case Requested is NULL then @IfNull else @IfNotNull end = 1;

Upvotes: 0

Siyual
Siyual

Reputation: 16917

Use both conditions in the WHERE clause via an OR statement. This creates a condition where the appropriate logic is used based on the value of the @Requested variable.

Select   ID 
From     SomeTable
Where    ((@Requested = 0  And Requested Is Null) 
    Or    (@Requested = 1  And Requested Is Not Null));

Upvotes: 5

Related Questions