Check if optional parameter was passed to stored procedure

Question

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
...

No - there's no general way of distinguishing if `@AdditiveIdentity` for example has the value `0` because the default was applied or because that value was passed explicitly. — Martin Smith, Oct 23 '17 at 19:39
No. You would need mandatory parameters and different procs. — Dan Guzman, Oct 23 '17 at 19:39
No, and why would you care? Either way you are going to work with the default value unless another value was specified, that is the point of having default values... — ATC, Oct 23 '17 at 20:23

score 0 · Accepted Answer · answered Oct 24 '17 at 06:18

0

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

answered Oct 24 '17 at 06:18

Stanislav Kundii

2,874
1
11
17

How do you believe this solves the question asked? There is no way of distinguishing if NULL was passed explicitly or added by the default. – Martin Smith Oct 25 '17 at 06:32
NULL passed explicitly or added by default, it does not matter – Stanislav Kundii Oct 25 '17 at 06:35
It’s just another sentinel value which the question was specifically asking how to avoid. – Martin Smith Oct 25 '17 at 06:48
1

Delete default values and always set value in execute proc :) – Stanislav Kundii Oct 25 '17 at 06:55
I turns out that even getting the default parameter value from the stored procedure definition is stupidly hard: https://stackoverflow.com/a/47485321/2799848 – Elaskanator Jul 20 '18 at 15:48

Check if optional parameter was passed to stored procedure

1 Answers1