Get all data where column is null, not null and both (null and not null)

Question

When @se_withsiddhi='Y' I am getting all the data but when @se_withsiddhi=null want to retrieve only null values but not getting.

   declare @se_withsiddhi varchar
        set @se_withsiddhi = 'Y'
    select  JD.Job_No,
            INV.Bill_No,
            inv.BilledToSE 
    from    invoice INV
            left outer join Job_Details JD on JD.Job_ID = INV.Job_ID 
    where   JD.Job_No is not null 
            and INV.Bill_No is not null 
            AND isnull(INV.BilledToSE,'') = case @se_withsiddhi
                                            when 'Y' then isnull(INV.BilledToSE,'')
                                            when Null then ''
                                            else  null
                                            end`enter code here`

[Recommended] (https://stackoverflow.com/questions/9581745/sql-is-null-and-null ) — ahmed abdelqader, Jun 11 '18 at 11:51

score 1 · Accepted Answer · answered Jun 11 '18 at 12:14

1

You need to use isnull for @se_withsiddhi:

case isnull(@se_withsiddhi, '')
when 'Y' then isnull(INV.BilledToSE,'')
when '' then ''
else  null
END

answered Jun 11 '18 at 12:14

Caldazar

2,801
13
21

score 0 · Answer 2 · answered Jun 11 '18 at 11:49

0

AND 
( 
   @se_withsiddhi='Y' or 
   @se_withsiddhi is null and INV.BilledToSE is null
)

answered Jun 11 '18 at 11:49

juergen d

201,996
37
293
362

Get all data where column is null, not null and both (null and not null)

2 Answers2