Sql Query with Parameters which might have value or can be null

Question

My table as follows:-

declare @table table(Inscode varchar(10), LocId varchar(10), effdate datetime)
insert into @table values('1','102','2023-02-01 00:00:00')
insert into @table values('1',NULL,NULL)

select * from @table

I am passing following parameters:-

declare @InsuranceCode VARCHAR(100)='1'
declare @LocationIds VARCHAR(100)=NULL
declare @EffectiveDate datetime='2023-02-01 00:00:00.000'

I want to built sql statement that is equivalent to

SELECT * from @table  where Inscode ='1' 
AND     LocId IS NULL
AND     effdate='2023-02-01 00:00:00.000' 

My @LocationIds can have value or can be null, similarly @EffectiveDate can have value or can be null. I tried following sql query, but I am not getting desired result

SELECT * from @table  where Inscode =@InsuranceCode 
AND     (LocId=@LocationIds OR LocId IS NULL)
AND     (effdate=@EffectiveDate OR effdate IS NULL)

Thank you

Answer 1

If you are passing a NULL value and you want to compare NULL to NULL you need to check both values are NULL:

SELECT Inscode,
       LocId,
       effdate
FROM @table
WHERE Inscode = @InsuranceCode
  AND (LocId = @LocationIds OR (@LocationIds IS NULL AND LocId IS NULL))
  AND (effdate = @EffectiveDate OR (@EffectiveDate IS NULL AND effdate IS NULL));

If you are on SQL Server 2022 (or an Azure hosted service), you could also use IS NOT DISTINCT FROM:

SELECT Inscode,
       LocId,
       effdate
FROM @table
WHERE Inscode = @InsuranceCode
  AND LocId IS NOT DISTINCT FROM @LocationIds
  AND effdate IS NOT DISTINCT FROM @EffectiveDate;