Filter parameter in stored procedure 2

[ncirkel]

Hi,

I'm trying to create a stored proc that accepts a filter parameter like "replies > 0". I know it can be done with putting the query in an exec function, but my select query is about 20 lines long. It would be a bit unhandy to put it all in between quotes. Is there another way to do this?

Thanks for your help!
Nander.

 

[JamesLean]

If you want to pass in the field and value like in your example then your only choice is to use dynamic SQL. However if you have certain fields that you want to search on then you can set up a procedure something like this:

CREATE PROC apGetUsers
  @userid int = NULL,
  @deptid int = NULL,
  @joindate datetime = NULL
AS

SELECT userid, firstname, lastname, deptid, joindate
FROM users
WHERE (@userid IS NULL OR userid = @userid)
  AND (@deptid IS NULL OR deptid = @deptid)
  AND (@joindate IS NULL OR joindate = @joindate)
ORDER BY userid
GO

So you set up your parameters with defaults of NULL, then you can pass in any combination of none, one or all parameters to narrow your search.

The WHERE clause only filters the column if a value has been passed in the parameter.

--James

 

[ncirkel]

Thanks, that's a lot better than what I have been trying!