Dynamic WHERE statement - how to? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Dynamic WHERE statement - how to?

Author

Topic

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-01-17 : 10:51:04

I have a basic query and I want to be able to adjust the WHERE statement on the fly.

SELECT EmployeeID, Name, Department, State
FROM Employees
WHERE Department = @Departnemt AND State = @State

I want to make the STATE portion optional, so if they included a value for STATE then it will use it, if not then I want to ignore it completely.

How can I accomplish this?

Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-17 : 10:53:12

[code]SELECT EmployeeID, Name, Department, State
FROM Employees
WHERE Department = @Departnemt AND (State = @State or IsNull(@State, '')='')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 11:41:54

[code]SELECT EmployeeID,
Name,
Department,
State
FROM Employees
WHERE Department = @Department
AND (State = @State OR @State IS NULL)[/code]

E 12°55'05.25"
N 56°04'39.16"

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-01-17 : 12:23:33

Thanks

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-01-17 : 12:39:29

Actually a follow up:

What if my WHERE statement was:
WHERE EndDate IS NULL

So what I want to do is look at employees who still work for us (EndDate IS NULL) or look at employees who no longer work for us EndDate IS NOT NULL.

How could that be handled?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 15:24:57

[code]SELECT EmployeeID,
Name,
Department,
State,
CASE WHEN EndDate IS NULL THEN 'Still working' ELSE 'Not working' END AS Status
FROM Employees
WHERE Department = @Department
AND (State = @State OR @State IS NULL)[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 02:32:47

www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources