Conditional WHERE Parameters

Using a stored procedure or other compiled SQL, have you wanted to be able to drop a specific condition in a WHERE clause and thought the only way was through multiple SQL Statements or through building a SQL statement with a VARCHAR string? Here is a method that will work for *almost* every condition:

Suppose I want to search an Employee table for values in these fields:

username
lastname
firstname
phone

Here is a proposed Stored procedure:

CREATE PROCEDURE "dbo"."EMPLOYEE_FILTER"
@username varchar(20) = NULL
, @lastname varchar(30) = NULL
, @firstname varchar(30) = NULL
, @phone varchar(20) = NULL
AS

SELECT
a.* -- always put field names!!!
FROM
"dbo"."EMPLOYEES" a
WHERE
((@username IS NULL) OR (a."username" = @username))
AND ((@lastname IS NULL) OR (a."lastname" = @lastname))
AND ((@firstname IS NULL) OR (a."firstname" = @firstname))
AND ((@phone IS NULL) OR (a."phone" = @phone))


Why this works:

SQL Server will short circuit AND/OR statements. In other words, if you are using OR and the first condition is TRUE, then it will not evaluate the seconds condition. Same thing goes for AND, if the first condition is FALSE, it won't evaluate the second condition. So, when @username IS NULL is evaluated as TRUE, it will not compare anything to the "username" field and move on to the next condition.

Possible issues:

If you ever *want* to see if a field is NULL, you can either use a different value to compare or use a different SQL statement:

-- only one internal OR clause will be run
WHERE
(
((@employee_absences IS NOT NULL)
OR (a."employee_absences" = @employee_absences))
OR ((@employee_absences IS NULL)
OR (a."employee_absences" IS NULL))
)

Source: David L. Penton
Viewed 8236 times