Parameters in Stored Procedures
This tips comes from Doug Nelson, in his blog entry “Optional Parameters in Stored Procedures“. I’ve used this extensively in MS SQL. According to the documentation, it should also works in Oracle and mySQL.
[Update on 4/23/2008: I notice that the link to Doug's post hasn't been working lately, so I wrote an article "CASE Statements in SQL Query", which covers some of the same concepts.]
To me, the most interesting thing is Doug’s posting is the clever use of the CASE statement. I follow his example in many of the stored procedures I write. By using this technique, I’ve saved a lot of time in trying to build where clauses for select statements, and it’s greatly cut down on the amount of SQL code I write.
Let’s say we have a table that contains a list of people, including firstname, lastname, address, city, state, zip. We want to search for people based on their firstname and/or lastname and/or the state they live in. We can do this with a single stored procedure by including parameters for the three possible search criteria . Simply determine the search criteria the user included (ie did they include a firstname in the search form), and pass that criteria to the stored procedure using the corresponding parameters. No more trying to dynamically build where clause based on what the use input into the search form!