CODEWITHSUNDEEP
sqlsql-servert-sql
Elaskanator
Elaskanator

Reputation: 1216

Check if optional parameter was passed to stored procedure

I really would prefer to not rely on sentinel values such as 0, -1, or NULL when testing if an optional parameter to a stored procedure was passed. Is there a definitive way to check?

For example, can I check if any of these sample parameters were specified in the call without assuming they were not passed with a particular value?

CREATE PROCEDURE TestProcedure
     @SampleStringParam VARCHAR(MAX) = NULL
    ,@SampleBooleanParam BIT = 0
    ,@MultiplicativeIdentity REAL = 1
    ,@MultiplicativeInverse REAL = -1
    ,@AdditiveIdentity REAL = 0
AS
...

Upvotes: 0

Views: 1376

Answers (1)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

CREATE PROCEDURE TestProcedure
     @SampleStringParam VARCHAR(MAX) = NULL
    ,@SampleBooleanParam BIT = NULL
    ,@MultiplicativeIdentity REAL = NULL
    ,@MultiplicativeInverse REAL = NULL
    ,@AdditiveIdentity REAL = NULL
AS
IF @SampleBooleanParam IS NULL
--  @SampleBooleanParam DEFAULT
SET @SampleBooleanParam = 0

Upvotes: 0

Related Questions