Recently, I was refactoring code and moving some embedded SQL into stored procedures. The SQL had a lot of optional parameters and it was handling it by modifying the WHERE clause like:

DECLARE @SQLString varchar(250)

 

SELECT @SQLString = ‘SELECT * FROM Employees WHERE ‘

 

IF @EmployeeId IS NOT NULL

    SET @SQLString = @SQLString + ‘ EmployeeID =  ‘ + cast(@EmployeeID, varchar(10))

 

IF @HireDate IS NOT NULL

    SET @SQLString = @SQLString + @HireDate + cast(@HireDate, varchar(10))

 

EXEC @SQLString

My first thought was repeat the same approach by concatenating strings together for the WHERE clause. But, now that I’m studying for my 70-229 exam, I’m a little more sensitive about performance tuning, and knowing that the Stored Proc can’t compile the Execution Plan for a SQL Statement like EXEC SqlString. So what approach to take? After pinging a DB friend of mine, she taught me a new approach to handling optional parameters in the WHERE clause with no string concatenation.

This example uses NorthWind:

DECLARE @EmployeeId int

DECLARE @OrderDate datetime

 

SET @EmployeeId = NULL

SET @OrderDate = NULL

 

SELECT *

FROM Orders

WHERE

(

    @EmployeeId IS NULL OR

    (@EmployeeId > 0 AND EmployeeId = @EmployeeId)

)

AND

(

    @OrderDate IS Null OR

    (@OrderDate IS Not Null AND OrderDate = @OrderDate )

)

 

SET @OrderDate = ‘07/04/1996′

And it filters the records.

This works because if the @EmployeeId IS NULL condition is true then the rest of the phrase is ignored. As cool as this is, it’s not without it’s downsides. Even so, until I find a better approach, I’m going to stick with this one. Any more suggestions?

Permalink | 1 Comment