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!

Advertisements

4 comments so far

  1. […] 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 […]

  2. […] CASE Statements in SQL Query for a method to write flexible stored […]

  3. […] Here is some sample code that would call the stored procedure from Case Statement in SQL Query […]

  4. SQL query Faqs on

    Nice tutorial. You can find more about SQL queries at

    SQL Query Question Answers


Comments are closed.

%d bloggers like this: