Dynamic Where clause

What is the best way to dynamically choose the where clause based on a
variable.
In the following test example, depending on @.i value, WHERE clause could
compare against being null or not null.
Another way to do would be writing ugly sql string like @.select + @.where
Please let me know.
TIA...
set nocount on
go
create table z_test_del
(
c1 int,
c2 int
)
go
insert z_test_del values(1,null)
insert z_test_del values(2,333)
insert z_test_del values(3,null)
insert z_test_del values(4,5555)
go
declare @.i int
set @.i = 0
if (@.i = 0)
select * from z_test_del where c2 is null
else
select * from z_test_del where c2 is not null
go
drop table z_test_del
go>> What is the best way to dynamically choose the where clause based on a
variable. <<
Dynamic is poor choice of words in SQL -- it implies that you are
writing code on the fly.
SELECT *c1, c2 -- never use * in production code!!
FROM Foobar
WHERE (c2 IS NULL AND @.flag = 0)
OR (c2 IS NOT NULL AND @.flag <> 0);|||You can use a CASE or use OR-ed predicates or write two separate statements.
For some alternatives refer to: http://www.sommarskog.se/dyn-search.html
Anith|||Thanks Joe.
"--CELKO--" wrote:

> variable. <<
> Dynamic is poor choice of words in SQL -- it implies that you are
> writing code on the fly.
> SELECT *c1, c2 -- never use * in production code!!
> FROM Foobar
> WHERE (c2 IS NULL AND @.flag = 0)
> OR (c2 IS NOT NULL AND @.flag <> 0);
>