where clause with multiple conditions sql server

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

SQL Server 2008 Forums

Transact-SQL (2008)

where clause with multiple conditions sql server

Author

Topic

teamjai
Yak Posting Veteran

52 Posts

Posted - 2012-01-03 : 01:56:46

Hi all,

I'm having a problem working the SQL out for the following:

I have a table Employee.

Eid---------Ename (Column name)
01----------AAA
02----------BBB
03----------CCC
04----------AAA
How can i handle multiple criteria query..
I have two text box for Search Function
text box1---- i give the search data like(01)
text box2----- i give the serach data like(CCC)

01 and CCC are in table data , so both criteria is true..
some time i give one criteria is true.....

i want know how to check the conditions. pls help, tell the query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-03 : 02:04:00

You can use null input parameters and COALESCE function in the where clause.

create proc sp1
@var1 varchar(5), @var2 int
as
select ...
from t1
where c1 = coalesce(@var1, c1) and c2 = coalesce(@var2), c2)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

teamjai
Yak Posting Veteran

52 Posts

Posted - 2012-01-03 : 02:24:39

thanks
this proc check only null parameter.
but i want check the data is true or false

quote:
Originally posted by tkizer

You can use null input parameters and COALESCE function in the where clause.

create proc sp1
@var1 varchar(5), @var2 int
as
select ...
from t1
where c1 = coalesce(@var1, c1) and c2 = coalesce(@var2), c2)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-03 : 12:00:28

That's what my example does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 12:03:43

shouldnt it be OR instead of AND as Ops explanation says he wants both the rows when he passes text box1=01 and textbox=CCC?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-03 : 12:13:28

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-03 : 12:17:14

quote:
Originally posted by visakh16

shouldnt it be OR instead of AND as Ops explanation says he wants both the rows when he passes text box1=01 and textbox=CCC?

OP didn't provided expected result set and isn't very clear. What I provided is what I understood out of the post. Gail's link is perfect for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources