Stored procedure default value output parameter is ignored?

98 views Asked by At

I have this simple procedure where I don't quite understand the behavior.

I want my procedure to return a default value if no other value is assigned to it.

CREATE PROCEDURE [dbo].[Test]
    @TestVariable int = 0 OUTPUT
AS
BEGIN
    SELECT @TestVariable
END

In my opinion this code should return 0. Since I put the default value to 0, but it returns NULL.

What is the point of this default value since it doesn't seem to affect the variable in any way?

The only solution I've found is to change it to this:

CREATE PROCEDURE [dbo].[Test]
    @TestVariable int = 0 OUTPUT
AS
BEGIN
    IF @TestVariable IS NULL 
        SET @TestVariable = 0

    SELECT @TestVariable
END

If that's the only way then fine, but what's the point of the default value inside the parameter block? If I create a stored procedure like this:

CREATE PROCEDURE [dbo].[Test2]
    @number1 int = 5,
    @number2 int = 5
AS
BEGIN
    SELECT @number1 + @number2
END

And don't pass in any variables the default values will be used and the returned result will be 10. If I change the @number1 And @number2 to OUTPUT parameters the answer will default to NULL again. I hope someone can clear up this confusing behavior for me.

I tried the same with normal parameters that aren't passed back after the procedure and those seem to work fine.

Also, I know I don't have to SELECT the output variable since it is returned automatically when I call the procedure, but it was just for testing purposes.

Execute statement:

DECLARE @Test int

EXEC [dbo].[Test] @TestVariable = @Test OUTPUT

SELECT @Test

ANSWER: Credit to @Panagiotis Kanavos

When trying to map the OUTPUT parameter to a variable it will take the variable and also take it as INPUT paramater like a normal variable. I wasn't aware of this double behaviour.

The default only works when no variable is mapped to the OUTPUT parameter and it's still used inside the stored procedure.

This post helped me understand the behaivour.

0

There are 0 answers