how do i build a dynamic where clause in a stored procedure? 2

[j9]

Hi,

I would like to build a where clause based upon parameters passed in to a stored procedure. Basically, each parameter corresponds to a column, and if a parameter is null or an empty string, I do not want it considered in the where clause (i.e. I want all values returned for its corresponding column).

Here is what I did as a quick and dirty fix, but now I'd like to do it the right way! Any help would be greatly appreciated!

CREATE PROCEDURE sprocGetMembers @isactive bit, @dept varchar(4),@lastname varchar(35) AS

SELECT *
FROM Member
WHERE IsActive LIKE CASE @isactive
WHEN NULL
THEN '%'
ELSE @isactive
END AND


EMPLOYEE_DEPARTMENT_NUMBER LIKE CASE @dept
WHEN ''
THEN '%'
ELSE @dept
END AND


LastName LIKE CASE @lastname
WHEN ''
THEN '%'
ELSE '%' + @lastname + '%'
END
GO

 

[rac2]

I think that is pretty clever. Here is how I handle this.

SELECT *
FROM Member 
WHERE 
      ( @isactive IS NULL OR IsActive = @isactive )
  AND ( @dept = '' OR EMPLOYEE_DEPARTMENT_NUMBER =  @dept )
  AND ( @lastname = '' OR
          LastName LIKE '%' +  @lastname + '%' )

It is about the same thing.

I like to use a slightly more general approach by introducing an indicator variable. Sometimes the column in the criterion does not have a special value that can be assumed to mean we dont care. Then I add a parameter which means, yes or no about using the criterion. That added parameter is the indicator variable. I like this approach because it separates the attribute "use this column in the condition" from "values of this column".

SELECT *
FROM Member 
WHERE 
      ( @anyIsActive = 1 OR IsActive = @isactive )
  AND ( @anyDept = 1' OR EMPLOYEE_DEPARTMENT_NUMBER =  @dept )
  AND ( @anyLastname = 1 OR
          LastName LIKE '%' +  @lastname + '%' )

I set the value of the indicator variable in my application, maybe it comes from a <SELECT> <OPTION value="-1">Any Status; or maybe it is set to 1 when a text box is empty

.

 

[MikeyJinPhx]

Hi All,

Just for the heck of it you might like to take a peek here. Pretty much Everything You Ever Wanted To Know About Dynamic SQL But Were (or weren't in this case!) Afraid To Ask...

http://www.sommarskog.se/dynamic_sql.html

Mike