Dynamic WHERE statement

If I pass a variable to @.Cost_Center that is 'SMS', 'SMP' OR 'ALL'
'ALL' will return records that have either 'SMS' or 'SMP' in them.
Now I am using an if statement that has the entire SQL statement in the body
of the
conditional.
I tried this
Where
IF (@.Cost_Center = 'All')
Begin
code...
and i.cost_center in ('SMS','SMP')
End
ELSE
Begin
code...
and i.cost_center = @.Cost_Center
End
Thanks
JimYou might want to check out the following article for some ideas:
http://www.sommarskog.se/dyn-search.html
Anith|||Thank you
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23glqcpZBFHA.4072@.TK2MSFTNGP10.phx.gbl...
> You might want to check out the following article for some ideas:
> http://www.sommarskog.se/dyn-search.html
> --
> Anith
>|||SELECT ...
FROM Foobar
WHERE cost_center = @.my_cost_center
OR cost_center
IN (CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMP' ELSE '' END,
CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMS' ELSE '' END));