Initializing Output Parameters in a Stored Procedure

Question

I have a stored procedure with an output parameter that I initialize with "0":

ALTER PROCEDURE dbo.SomeProcedure @someparam INT = 0 OUT ... 

However, when the procedure does not change or set this parameter at run time, the output value is NULL, not "0", as expected.

Is it mandatory to set a default value in the procedure code

 SET @someparam = 0; 

and avoid initialization in the declaration?

Why does SQL Server allow you to enter default values ​​in the declaration for output parameters?

Answer 1

The syntax you are showing is not for initialization, for the default value for missing parameters :

default The default value for the parameter. If a default value is specified for a parameter, the procedure can be performed without specifying a value for this parameter .

So then it should be clear that if you can read the output value, it should be so that you provide a parameter for @someparam and therefore it is not "missing". If this is not "missing", it is not assigned a default value, since it should already have a value of one of your caller frame (possibly NULL). Q.E.D.

The following code shows the difference and illustrates the behavior that you see:

 create procedure usp_test @p int = 0 output as set @p += 1; select @p; go exec usp_test; go declare @unitialized int; exec usp_test @unitialized output; go