Optional items in a Where clause

Here is a common situation. You have a query that has several possible but optional filters. For example you may filter on the first name or last name of both. Now to do this there are several possible technics. You could
1. assemble a dynamic SQL statement, you could
2. use the if statment to execute a different select statement or
3. use conditions in the where clause to handle it in one statement

Dynamic SQL
CREATE PROCEDURE Dynamic_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

DECLARE @sql nvarchar(1000)

SELECT @sql = 'SELECT * FROM Users u WHERE 1=1 '

IF @FirstName IS NOT NULL

BEGIN

SELECT @sql = @sql + ' AND FirstName =''' + @Firstname + ''''

END



IF @LastName IS NOT NULL

BEGIN

SELECT @sql = @sql + ' AND LastName =''' + @Lastname + ''''

END



print @SQL

EXEC sp_executesql @sql


The above proc does what it is supposed to do but it has a couple of drawbacks. Firstly it lacks elegance. Secondly it totally defeats the whole purpose of using a stored procedure. The dynamic SQL requires a compile, there is a significant security risk and lastly it is really hard to follow. If you are going to do this you might as well just do it in the compiled VB/C# code.

Use a Where Clause
CREATE PROCEDURE IF_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

SELECT * FROM Users u WHERE FirstName = @FirstName AND LastName = @LastName

END



IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

SELECT * FROM Users u WHERE LastName = @LastName

END



IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

SELECT * FROM Users u WHERE FirstName = @FirstName

END



IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

SELECT * FROM Users u

END


Again this works but, as you can imagine, any more than two paramaters and it get unwieldy. Also, the if clause can force a recompile.


WHERE Clause
ALTER PROCEDURE Where_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

SELECT * FROM Users u

WHERE (FirstName = @FirstName OR @FirstName IS NULL)

AND (LastName = @LastName OR @LastName IS NULL)




Again this works and is a much more elagant solution. The code is tight, easy to follow and performs well. The main problem with this is that the compiler will factor in all of the where conditions into it's execution plan. That is, if there are tables needed only for the where clause, they will be processed even if that where condition is not needed. However, IMHO this is the way to do it for 90% of cases.