Is it possible to ignore an output param of a stored procedure?

Question

How can I ignore an output parameter of a stored procedure. I'm calling the sp from another sp.

e.g.:

DECLARE @param1 integer

EXEC mystoredprocedure @in_param_1, @in_param2_, @param1 OUTPUT, what do I type here to ignore the second output param

I'm using T-SQL (MS SQL 2005).

Thanks for your help.

Answer 1

What's stopping you from letting is pass the value to @param1 and simply not using it anymore? I don't believe sprocs support overloading nor optional params.

Answer 2

You'll probably have to just ignore the OUTPUT param yourself by just not doing anything with the value. It's not like the overhead of that variable is an issue or anything. The only issue here is that your code will be a little bit uglier. So slap a comment on there explaining that the OUTPUT param isn't being used and everything should be alright.

Answer 3

The output parameter has to have a default in order for you to not pass it. See below

create proc x (@in int = null, @out int = null OUTPUT)
as 
Begin
    Select @in
End

exec x 1

EDIT

To clarify a little, the error is being returned because the stored procedure that is being called has a parameter defined (in this case @param1), for which no default is defined. (i.e. @param1 int rather than @param int = -1) In the case where neither a parameter or default is specified for a defined parameter of a stored procedure when calling the stored procedure an error will occur. The same thing would happen if you tired to omit an input parameter that does not have a default specified.

Answer 4

Yeah, I basically don't want to declare a variable I'm not going to use. Call me fanatic.

Answer 5

You can just use NULL as the last parameter, and it should work fine - as long as that parameter isn't expected for input logic in the proc as well.

In your case, you'd call the proc as

exec x 1, null

Here's another example that for the same scenario...

create proc MyTempProc
    (@one int,
    @two int out,
    @three int out)
AS
begin

    set @two = 2
    set @three = 3

    select @one as One

end
go

declare @p1 int,
     @p2 int

set  @p1 = 1

exec MyTempProc @p1, @p2 out, null 

print @p1
print @p2

Answer 6

If the SP you're calling expects a parameter to be passed, you have to have one there. Even if you disregard the output of it, it is part of the structure of the SP.

Think of parameters as a "Data Contract". If they're not defaulted, they're required. Even if the values are disregarded.

Forcing you to declare a dummy value you'll never read is the cost of calling that stored proc that may be used by something that DOES need to utilize the value of the output parameter.

Answer 7

The second part of the answer (by Scott Ivey) doesn't work. You will get this error: "Msg 119, Level 15, State 1, Line 1 Must pass parameter number %d and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'."