Optional Parameters in SQL Stored Procedures - Robert McLaws: FunWithCoding.NET: "Optional Parameters in SQL Stored Procedures"
CREATE PROCEDURE TestProc
( @Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL)AS
SELECT *
FROM TestTable
WHERE ((@Param1 IS NULL) OR (col1 = @Param1)) AND ((@Param2 IS NULL) OR (col2 = @Param2)) AND ((@Param3 IS NULL) OR (col3 = @Param3))
Results in:
exec TestProc
exec TestProc I
exec TestProc I, Love
exec TestProc I, Love, SPROCs
www.visli.com
1 comment:
another version..
CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT
*
FROM
TestTable
WHERE col1 = ISNull( @Param1,col1 )
AND col2 = ISNull( @Param2,col2 )
AND col3 = ISNull( @Param3,col3 )
==================
WHERE @Param1 IN (Col1, NULL)
Post a Comment