CODEWITHSUNDEEP
t-sql
Dimt
Dimt

Reputation: 2328

Dynamic WHERE condition Is Null or Is Not Null

I have a stored procedure which takes a @flag as a parameter. That flag supposes to indicate to select null values or none null values.

for none null values my solution looks like that:

@Flag int
SET @Flag = NULL

WHERE   ISNULL(column1,'') = ISNULL(@Flag,'')

Is there a way to accommodate none null values in similar manner ? If no what would be the most compact solution ?

Upvotes: 0

Views: 577

Answers (2)

jean
jean

Reputation: 4350

Flag int
SET @Flag = NULL

select * from table
WHERE (@flag is null and column is null) or ((@flag is not null and column is not null) and @flag = column)

I advise to NEVER SET ANSI_NULL OFF, NEVER! That can lead to a lot of unnecessary maintenance pain.

No need for a compact solution like

ISNULL(column1,'') = ISNULL(@Flag,'')

Also that ill return null rows when @flag = '' and also ill return '' rows when flag is null

Upvotes: 1

Lowkey
Lowkey

Reputation: 836

The only way I can think of is SET ANSI_NULL OFF and then do comparison: column1 = @flag

Upvotes: 0

Related Questions