Recently, we discussed how to call procedures with OUTPUT parameter. Now, we’ll cover a nuance in the same path. Default parameter values in Stored Procedures.
When we call a procedure that has DEFAULT values defined for its parameters, the way we call the procedure could change what values goes go into the execution of the procedure.
Variation 1: Purpose of DEFAULT values
DEFAULT values are defined in Store Procedures definition, so when no value is provided in procedure call, default values could be used (during execution).
Sample:
Consider the below sample procedure definition code below:
CREATE PROCEDURE dbo.abc
@Param1 INT = 10
...
Parameter @Param1 is defined with 10 as the DEFAULT value. So, when I call the procedure as:
EXEC dbo.abc
GO
The procedure takes the DEFAULT value 10 in its execution.
Variation 2 : Values in procedure call
When we actually provide any values in procedure call, the new value provided in the call is used in procedure execution, rather than the default value
DECLARE @P1 INT = 15
EXEC dbo.abc @P1
Now, in procedure execution, the new value (15) is used, and not the default value (10). This makes sense. Every time we need to run the procedure with different values, we just provide them in the parameter and it takes into effect; Where no value is provided, default values kick-in.
Variation 3 : NULL Value in procedure call
When we call the procedure with parameters, but do not provide any value, the default NULL value, assigned during variable declarations, will go in as the value.
DECLARE @P1 INT
EXEC dbo.abc @P1
GO
In this case, when the variable @P1 is declared, by default, NULL value is assigned. Since we do not have a subsequent step to assign value, the NULL value will go into the procedure call. This NULL value will overwrite the default value (10) mentioned in procedure definition.
So, if you want to run with default values defined in procedure definition, just run it like this:
EXEC dbo.abc
GO
Complete Code
For more testing, use the below complete code :
--
-- Sample procedure to check the default values
--
CREATE PROCEDURE dbo.abc
@Param1 INT = 10
, @Param2 INT OUTPUT
AS
SET @Param2 = @Param1
SELECT @Param1, @Param2
GO
--
-- Variation 1: Purpose of DEFAULT values
--
EXEC dbo.abc
GO
--
-- Variation 2 : Values in procedure call
--
DECLARE @P1 INT = 1
, @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO
--
-- Variation 3 : NULL Value in procedure call
--
DECLARE @P1 INT
, @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO
Read Full Post »
Playing with database servers... 