Optional Parameters in SQL Stored Procedures
Want to create a stored procedure that searches for specific, but optional, values? I had to do it today for a web project I'm working on (I hate not being able to talk about projects I'm working on) and Terry Denham, the SQL Guru without a blog, came to my rescue yet again.
Because the WHERE clause has secluded OR statements buttressed by a "trueism" (Terry's term) of 1 = 1 (does anyone know of a case where one DOESN'T equal one?), the statement will always execute, even if you don't pass anything in. Pretty cool, huh? Thanks Terry! Saved my butt again.
CREATE PROCEDURE TestProc
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
((@Param1 IS NULL) OR (col1 = @Param1)) AND
((@Param2 IS NULL) OR (col2 = @Param2)) AND
((@Param3 IS NULL) OR (col3 = @Param3))
exec TestProc I
exec TestProc I, Love
exec TestProc I, Love, SPROCs
UPDATE: The procedure above was updated to correct a logical flaw in Terry's example.You cannot use a “trueism” because you will always get back all rows no matter what parameters you pass in.While not as performant as Dynamic SQL (blah blah blah) it does the trick in situations like mine where direct table operations are not allowed for security reasons.