Archive for the ‘Stored Procedures’ Category

CASE Statement in SQL Query

I’ve used SQL to varying degrees for a number of years. However, one of the features I’ve recently learned about is the CASE statement. For some reason, I missed this powerful feature when I studied SQL.

When would you use the CASE statement? I use it when writing stored procedures when I want to have optional input parameters. Consider this simple stored procedure:

ALTER PROCEDURE [dbo].[GetRooms]
(
@roomID int = 0,
@color varchar = ''
@type varchar =''
)
AS
BEGIN
SELECT roomID, roomName, roomColor, roomType, roomWidth, roomLength, roomheight
FROM tblRooms
WHERE
roomID = CASE @roomID WHEN 0 THEN roomID ELSE @roomID END
and roomColor = CASE @color WHEN '' THEN roomColor ELSE @color END
and roomType = CASE @type WHEN '' THEN roomType ELSE @type END
ORDER BY roomName
END

With this one Stored Procedure, we have a lot of options for what is returned, depending on what parameters we include in the call.

EXEC GetRooms – Returns all the rooms in the database.
EXEC GetRooms @roomID=3 – Returns the room with ID of 3
EXEC GetRooms @color=’blue’, @type=’kitchen’ – Returns all the kitchens that are blue

With the optional parameters, this one stored procedure can be used in a number of different scenarios. Using this technique, I once replaced about a dozen stored procedures in a database with just one. How cool is that!

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!

Stored Procedures in SQL

Most of the projects I work on use MS SQL server as a data store. When setting up a database, I usually create stored procedures to interact with the database. There are a number of reasons I do this:

  1. Limit access to the database. By using stored procedures, we can give the web user account access to execute the stored procedures without giving them datareader or datawriter access. This means that the only way this account can interact with the database is through the stored procedure. Very much limits the possibility of getting hit by a SQL injection attack.
  2. All interactions with the database are parameterized. Again, this limits the possibility of getting attacked by SQL Injection.
  3. Keeps the database separate from your source code. Especially important if you have multiple applications that access the same database. This allows you to make changes to the stored procedure without making changes to the application(s). This works as long as you don’t change the parameters or structure of the return results of the procedure.
  4. Makes it easy to do multiple things with a single database call. You can even build transactions in the stored procedure when you need to ensure that all queries are successful once the first one runs. If one of the queries fail, then the entire transaction is rolled back.
  5. For projects with a Data Access Layer (DAL), the DAL is almost trivial to write. Simply call the stored procedures and pass back the results.

For some projects, using stored procedures can add too much complexity. However, I tend to error on this side rather than putting too much SQL into the application’s code.