Shorthand IF expressions in sql

Question

I currently have a saved proc that accepts a string

@vari as varchar(30)
if @vari is null 
    SELECT * FROM TABLE 
else 
    SELECT * FROM TABLE WHERE col = @vari
endif

is there any way to embed the if statement, thereby not declaring 2 select just because of 1 param?

+5

Alex May 11 '12 at 13:58

2 answers

Assuming it is colnever NULL, you can do this:

select *
from table
where col = isnull(@vari, col)

+3

Redfilter May 11 '12 at 14:04

Nikola Markovinović · Accepted Answer · 2012-05-11T14:00:15+0000

SELECT * FROM TABLE WHERE (@vari is null or col = @vari)