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

I am trying to achieve something like the below in WHERE clause in sql.

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

I tried the following:

WHERE ((@zipCode IS NOT NULL AND ([Portal].[dbo].[Address].PostalCode=@zipCode)) OR (@zipCode IS NULL AND ([Portal].[dbo].[Address].Position.Filter(@radiusBuff) = 1)))

which is wrong. Can anyone help in framing the exact statement. Thanks!

Answers


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');

-- fails
select *
from @Values
where id = @Int

-- works fine
select *
from @Values
where 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 @Values
where id = isnull(@Int, 8)
and def like isnull(cast(@Int as varchar), '%repeat%')

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)   
  end
else 
  begin
    ([Portal].[dbo].[Address].PostalCode=@zipCode )
  end  

Need Your Help

jQuery CSS Opacity Animate

javascript jquery css

So I'm trying to create a simple fading slideshow with five slides that repeats when finished.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.