A problem that has vexed me for a long time, but which I recently solved, is how to create a SQL Stored Procedure that allows me to specify an arbitrary number of parameters, such as for a report or advanced search engine. As I mentioned at the end of my last article on stored procedures, building a custom SQL statement within the stored procedure is an option, certainly not an optimal one (for the reasons cited in the last article). I have, as I mentioned, solved this problem. I believe, although I'm open to any arguments to the contrary, that my solution performs well is generally sound. I look forward to your comments on this technique -- please use the Feedback link at the top of the page to send them to me.

To provide some background, let's say you've got a Star Wars website and you want your users to be able to find things. So you create a search engine, and you let people search your database of, say, toys using any or all or none of the following criteria: Gender, Species, Affiliation, Date Released (minimum), Maximum Price. So, you have several drop down boxes and textboxes available along with a "Search" button. If the user just hits search, you bring back everything in your database. If they choose to narrow it down, they can do so using any or all of the criteria available.

Now, if this were a typical ASP neophyte application, we would expect to see something like this:
(asp code)

'BAD EXAMPLE; WHAT NOT TO DO
sql = "SELECT * FROM swToys WHERE Disable = 0 "
If Request("gender") <> "" Then sql = sql & "AND gender = '" & Request("gender") & "' "
If Request("species") <> "" Then sql = sql & "AND species = '" & Request("species") & "' "
If Request("affiliation") <> "" Then sql = sql & "AND affiliation = '" & Request("affiliation") & "' "
If Request("date_released") <> "" Then sql = sql & "AND date_released = '" & Request("date_released") & "' "
If Request("maximum_price") <> "" Then sql = sql & "AND price > '" & Request("maximum_price") & "' "

objRs.Open sql, objConn

This is bad. For one thing, string concatenations are expensive. For another, hitting the Request object more than once for a given value is inefficient. And yet another thing, it's inefficient to use SELECT * -- you should instead specify the columns you need. Finally, you should really try as hard as possible to avoid putting raw SQL into your ASP logic. At the very least you should use a stored procedure, and at best you should be calling a component to decouple your ASP page from the back end data.

Instead of building the sql statement from scratch in your application logic, build your stored procedure with the necessary flexibility. Consider this as an alternative:

CREATE PROCEDURE GetToys
(
  @gender varchar(10) = null,
  @species varchar(50) = null,
  @affiliation varchar(50) = null,
  @min_date_released datetime = null,
  @max_price money = null,
  @maxrows int = 0
)
AS
  -- What's this do?
  SET ROWCOUNT @maxrows
  
  SELECT 
    toy_name,
    toy_description
  FROM
    swToys
  WHERE
    gender = IsNull(@gender, gender)
  AND
	species = IsNull(@species, species)
  AND
	affiliation = IsNull(@affiliation, affiliation)
  AND
	date_released >= IsNull(@min_date_released, date_released)
  AND
	price <= IsNull(@max_price, price)
  AND
    disable = 0

By setting all of the parameters to default to null if they are not passed, it makes them optional but still lets us determine if anything was passed for them. Then, by using IsNull and converting a null value to the value of the column being compared, we are guaranteed of a match whenever a null value is passed (note - there must some form of an equality statement involved for this to work) into the sproc. For example, if no parameters are passed into the above stored procedure, it will evaluate the same as if we had executed "SELECT toy_name, toy_description FROM swToys WHERE disable = 0".

Note: One condition to be careful of is wherever you may have NULL values being compared with NULL values. The database can be configured to handle this in more than one way (i.e. for some database servers it may evaluate to TRUE and for others FALSE). Watch for this if you run into unexpected behavior when using this technique, and talk to your DBA (or bust out the BooksOnline) for help configuring this setting the way you would like it.

That's really all there is to this. It's so simple that I'm a bit mad at myself for not having thought of it before, and I hope that there isn't some flaw in this technique that I'm as yet unaware of. I've been using this in several applications since June 2001 without any problems.