Default value not returned 1

[tbode2006]

ALTER PROCEDURE [dbo].[ap_myTest]
	(	 @my_in	int
		,@my_out int = 10 output
	)
AS
BEGIN
	IF @my_in = 5
	BEGIN
		SET @my_out = 7
	END
END

exec ap_myTest 5 @my_out = 7
exec ap_myTest 6 @my_out = null
exec ap_myTest 5,12 @my_out = 7
exec ap_myTest 6,12 @my_out = 12

My question is: Why does @my_out not equal the DEFAULT value of 10 when @my_in = 6?

 

[nickdel]

tbode, I added a select and it works fine for me!

alter PROCEDURE [dbo].[ap_myTest]
    (     @my_in    int
        ,@my_out int = 10 output
    )
AS
BEGIN
    IF @my_in = 5
    BEGIN
        SET @my_out = 7
    END
	SELECT @my_out
END

Nick

 

[tbode2006]

So adding the SELECT makes it work. My question is why did you have to? What is the purpose of defining a default value if it is not used implicitly?

 

[gmmastros]

default parameters can be a little weird, especially when used the way you are trying to use it.

When you want to use EXEC to run a stored procedure AND return a value, you must create a variable (in the calling routine) and pass that variable to the procedure. The problem is... passing the variable also passes the value of the variable, so the default parameter is NOT used.

Let me show you a couple examples.

Create this procedure:

Create Procedure Test_Default
	@DefaultParameter int = 10
As
Select @DefaultParameter

Now test it with the following

[tt][blue]
Exec Test_Default [green]-- Returns 10[/green]
Exec Test_Default 20 [green]-- Returns 20[/green]
Exec Test_Default NULL [green]-- Returns NULL[/green]
[/blue][/tt]

So, if you pass any value to the procedure, the default does not take affect.

Now, let's look at procedures with output parameters.

Create this procedure.

Create Procedure Test_OutputParameter
	@InputParameter int,
	@OutputParameter int output
As
Set @OutputParameter = @InputParameter

To test it....
[tt][blue]
Declare @Output int
Exec Test_OutputParameter 10, @Output Out

Select @Output [green]-- Returns 10[/green]
[/blue][/tt]

Note that the only way to return an output parameter is if you pass in a parameter and put OUT (or Output) on the line.

So, what have we figured out...

The only way to return an output parameter from a stored procedure is if you pass it a variable. Every variable MUSt have a value (even if the value is NULL). So, you cannot have a default output parameter and expect it to work.

There are things you can do, though. For example, you could sacrifice NULL as the 'default'. Like this...

ALTER PROCEDURE [dbo].[ap_myTest]
    (     @my_in    int
        ,@my_out int output
    )
AS
BEGIN
    [green]-- Set the default here[/green]
    Set @my_out = Coalesce(@my_out, 10)
    IF @my_in = 5
    BEGIN
        SET @my_out = 7
    END
END

The coalesce function will return the actual value for @my_out if it is NOT NULL. If it is NULL, then the value 10 will be used.

Does this make sense? If not, let me know and I will explain more.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tbode2006]

When you want to use EXEC to run a stored procedure AND return a value, you must create a variable (in the calling routine) and pass that variable to the procedure. The problem is... passing the variable also passes the value of the variable, so the default parameter is NOT used.

The only way to return an output parameter from a stored procedure is if you pass it a variable. Every variable MUSt have a value (even if the value is NULL). So, you cannot have a default output parameter and expect it to work.

So what you are saying is that there is no reason to define a default value in the parameter definition for an OUTPUT since it is overridden. I have my procedure working correctly but just wanted some insight to why my initial attempt did not work. Thanks for the explanation George. Have a star!

 

[gmmastros]

>> So what you are saying is that there is no reason to define a default value in the parameter definition for an OUTPUT since it is overridden.

Yes. That is what I am saying.

To use a default parameter, it must be missing. To use an Output parameter, it must exist.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom