Where Clause Help? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where Clause Help?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-08-16 : 07:00:16

Hi,

I have a Select Query with couple of where conditions as follows.

Select * From Table1 t
Where t.condition1 = parameter1
and t.condition2 = parameter2
and t.condition3 = parameter3

I can use parameters or NOT.How I can run the select query if I dont send parameter1 and parameter2???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-16 : 07:06:10

are you thinking of something like:


select * from table1 t
where
   (t.condition1 = @parameter1 or @parameter1 is null)
   and (t.condition2 = @parameter2 or @parameter2 is null)
   and t.condition3 = @parameter3

There is some useful information along with discussion of performance issues that you may run into here: http://www.sommarskog.se/dyn-search-2005.html

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-08-16 : 07:07:31

hi,

thanks for your reply. When I write like yours, Can I still run the query if no parameter1 and parameter2???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-16 : 07:12:34

You will still need to declare the parameter (or have it as a parameter for of the stored procedure), but you wouldn't need to supply any values. For example, this is valid:

DECLARE @parameter1 INT;
SELECT * FROM table1 WHERE (t.condition1 = @parameter1 or @parameter1 is null)

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-08-16 : 07:25:18

thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources