check column is null or not null in conditional?

Ramesh

4y862SQL

Hi everyone, how to check column is null or not null in conditional?
I need to convert Dynamic to Static SQL

Dynamic SQL:

IF @typeWeb = 1
       BEGIN
           SET @where = @where + ' AND p.Dealer is null '
       END
ELSE
IF @typeWeb = 0
       BEGIN
           SET @where = @where + ' AND p.Dealer is not null '
       END

How can i type this code?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Or:
set @where = @where + case @typeweb when 1 then ' and p.Dealer is null' when 0 then ' and p.Dealer is not null' else '' end
+2
Nitin Sontakke4y ago
Or is here is yet another complex way of achieving the same result:
and 1 = case when @typeweb = 1 and p.Dealer is null then 1 when @typeweb = 0 and p.Dealer is not null then 1 else 0 end
This is NOT for dynamic SQL, a normal or usual one.
For dynamic one, use following instead:
set @where = @where + case when @typeweb = 1 then ' and p.Dealer is null' when @typeweb = 0 then ' and p.Dealer is not null' else '' end
Hope it helps!
+2
Rajanikant Hawaldar4y ago
1. (typeWeb = 1 AND p.Dealer is null) OR (typeWeb = 0 AND p.Dealer is not null)
+2