Wednesday, May 7, 2008

Optional Parameters in SQL Stored Procedures

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
at

1 comment:

  1. RajeevMay 7, 2008 at 9:13 AM

    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)

    ReplyDelete