Technical Tips and Solutions
14/10/2008 - Optional Parameters in SQL Server Stored Procedures
Sometimes you just don't know how many parameters in your query will be passed. It's easy to set up optional parameters in a SQL Server Stored Procedure, but a bit more tricky to make the where clause optional. Here's a solution, found at Bugsplat.
CREATE PROCEDURE [selOptionalWhere]
@parm1 varchar(10) = null, -- these are optional parms
@parm2 varchar(10) = null,
@parm2 datetime = null
-- ...... and other parms you need
AS
SELECT * FROM someTable st
WHERE
(@parm1 is null OR st.field1 = @parm1) AND
(@parm2 is null OR st.field2 = @parm2) AND
(@parm3 is null OR st.datefield >= @parm3)
ORDER BY st.field1, st.field2