Coding stored procedure for search screen with multiple, optional criteria

Question

I've got a search screen on which the user can specify any combination of first name, last name, semester, or course. I'm not sure how to optimally code the SQL Server 2005 stored procedure to handle these potentially optional parameters. What's the most efficient way? Separate procedures for each combination? Taking the items in as nullable parms and building dynamic SQL?

Answer 1

I would do it with sp_executesql because the plan will be cached just for the first pattern, or the first set of conditions.

Take a look at this TechNet article:

*sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.*

Answer 2

I'd set each parameter to optional (default value being null)

and then handle it in the WHERE....

FirstName=ISNULL(@FirstName,FirstName)
AND
LastName=ISNULL(@LastName,LastName)
AND
SemesterID=ISNULL(@SemesterID,SemesterID)

That'll handle only first name, only last name, all three, etc., etc.

It's also a lot more pretty/manageable/robust than building the SQL string dynamically and executing that.

Answer 3

Was just posting the same concept as Kevin Fairchild, that is how we typically handle it.

You could do dynamic sql in your code to create the statement as required but if so you need to watch for sql injection.

Answer 4

As muerte points out, the plan will be cached for the first set of parameters. This can lead to bad performance when its run each subsequent time using alternate parameters. To resolve that use the WITH RECOMPILE option on the procedure.

Answer 5

The best solution is to utilize sp_execute_sql. For example:

--BEGIN SQL
declare @sql nvarchar(4000)

set @sql = 
'select * from weblogs.dbo.vwlogs 
where Log_time between @BeginDate and @EndDate'
+ case when @UserName is null then '' else 'and client_user = @UserName' end

sp_execute_sql
@sql
, @params = '@UserName varchar(50)'
, @UserName = @UserName
--END SQL

As muerte mentioned, this will have a performance benefit versus exec()'ing a similar statement.