A dynamic WHERE clause in a SP? - 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 2000 Forums

Transact-SQL (2000)

A dynamic WHERE clause in a SP?

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2005-10-31 : 09:52:36

I have a stored procedure that returns a select query.

I need to pass in a variable that determines whether it is filtered, and by what:

e.g.

CREATE PROCEDURE sp_stafflistselect
@JobID integer=0
AS
SELECT * FROM StaffList
WHERE JobID=@JobID
ORDER BY Lastname,Firstname
GO

The bit I'm trying to figure out is that I don't want to use the clause if @JobID=0, which here is representing 'all'.

Any ideas?
Thanks
Mark

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-31 : 10:13:56

could you say something along the lines of

If @JobID= 0 then
BEGIN
SELECT * FROM StaffList
ORDER BY Lastname,Firstname
END
GO

If @JobID <> 0 then
BEGIN
SELECT * FROM StaffList
WHERE JobID=@JobID
ORDER BY Lastname,Firstname
END
GO

mark1504
Posting Yak Master

103 Posts

Posted - 2005-10-31 : 11:19:34

The code I listed was just an example. The actual select statement extends to 60 lines! Also JobID is 1 of 2 filters I'm trying to add, so with all combinations I'd need 8 x 60 lines.

I've just come up with a workaround, which is nearly okay, but doesn't yet handle nulls. Perhaps you could take a look at this?

CREATE PROCEDURE sp_stafflistselect
@JobID integer=0

AS
DECLARE @UJobID AS Integer
IF @JobID=0 SET @UJobID=99999999 ELSE SET @UJobID=@JobID

SELECT * FROM StaffList
WHERE JobID>=@JobID AND @JobID<=@UJobID
ORDER BY Lastname,Firstname
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-31 : 12:18:22

WHERE (@JobID =0) or (JobID = @JobID)

mark1504
Posting Yak Master

103 Posts

Posted - 2005-10-31 : 12:43:12

Simple yet brilliant! Thank you doctor.

CASE CLOSED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources