SQL SERVER: Check if variable is null and then assign statement for Where Clause

is null is the syntax I use for such things, when COALESCE is of no help.

Try:

if (@zipCode is null)  begin    ([Portal].[dbo].[Address].Position.Filter(@radiusBuff) = 1)     endelse   begin    ([Portal].[dbo].[Address].PostalCode=@zipCode )  end  

Isnull() syntax is built in for this kind of thing.

declare @Int int = null;declare @Values table ( id int, def varchar(8) )insert into @Values values (8, 'I am 8');-- failsselect *from @Valueswhere id = @Int-- works fineselect *from @Valueswhere id = isnull(@Int, 8);

For your example keep in mind you can change scope to be yet another where predicate off of a different variable for complex boolean logic. Only caveat is you need to cast it differently if you need to examine for a different data type. So if I add another row but wish to specify int of 8 AND also the reference of text similar to 'repeat' I can do that with a reference again back to the 'isnull' of the first variable yet return an entirely different result data type for a different reference to a different field.

declare @Int int = null;declare @Values table ( id int, def varchar(16) )insert into @Values values (8, 'I am 8'), (8, 'I am 8 repeat');select *from @Valueswhere id = isnull(@Int, 8)and def like isnull(cast(@Int as varchar), '%repeat%')

Try a case statement

WHERECASE WHEN @zipCode IS NULL THEN 1ELSE @zipCodeEND