Wednesday, February 7, 2007

MS SQL server: Optional Search Parameters


create proc myproc (
val_1 int, -- required
val_2 varchar(32), -- optional
val_3 int -- optional
) as

select *
from mytable
where
col_1 = val_1
and col_2 like case
when val_2 = '' then col_2
else '%' + val_2 + '%'
end
and col_3 = case
when val_3 = 0 then col_3
else val_3
end
go

No comments: