Brian Webster - Blog

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!