SQL Server Performance Forum – Threads Archive
Optional Stored Procedure Parameters
Over the last year or so, I’ve settled on a fairly nifty approach allowing me to pass optional parameters to Stored Procedures. I primarily use this approach when authoring stored procedures to support Reporting Services reports, where I may allow the user to optionally select values for many parameters on the report. I used to accomodate optional parameters by writing dynamic sql, but often, that seemed like too much work and I was concerned about the performance cost of using dynamic sql (because it is not compiled). The following procedure demonstrates the approach I almost always use now. Notice the Where clause… I use an OR statement so that if a parameter is NULL, it will not limit the results of the query. I also initialize the parameters, allowing other custom "wild card" values to be passed and treated as NULL values upon execution. This gives me more flexibility in the report parameter configuration… Create Procedure uspCustomer_sel(@Country nvarchar(15) = NULL,
@Region nvarchar(15) = NULL,
@City nvarchar(15) = NULL) AS –Initialize Parameter Values
If @Country = ” OR @Country = ‘*’
SET @Country = NULL
If @Region = ” OR @Region = ‘*’
SET @Region = NULL
If @City = ” OR @City = ‘*’
SET @City = NULL Select *
From Customers
Where
(@Country IS NULL OR Country = @Country)
AND (@Region IS NULL OR Region = @Region)
AND (@City IS NULL OR City = @City)
I like this approach because…
- it is quick and easy to write and read,
- it is easy to work around some of the challenges related to passing NULL values for Reporting Services report parameters by including some logic to initialize the parameters,
- it is compiled and should perform better (right?)
Every time I write one of these procedures, I argue with myself and often wonder if there’s a better way. Sometimes when I use this approach with alot of parameters (say more that 5 or 10), I suffer performance. I’ve resorted back to dynamic sql in a few cases. My most popular question to ponder is "if a parameter is NULL, does it still have to evaluate if the column is equal to the parameter?". If the answer is yes, I can definately see how this will get you in trouble with large datasets and multiple parameters, and I suspect the answer is yes… So, I must ask, when it comes to optional parameters, are the two approaches I’ve used the only (or best) ones avaliable? Is there a smarter way to use the OR statment in this example? Do I need to resort to dynamic sql for more complex queries? I welcome your thoughts and feedback. Thanks,
Monte
Monte Holyfield
MJH Software Solutions LLC
www.mjhsoftware.com
303.666.1212 – office
There is another approach used in company I worked for. They approach was to prepare template and run template runner to produce different stored procedure with the code you would create and run as dynamic sql. All ifs they would put on client code and in each branch different stored procedure produced by template runner would be executed.
]]>