Receiving error on nested Stored Proc with OUTPUT 1

[bowline]

I am trying to create a stored procedure (sp_ProcA) that calls another stored procedure (sp_ProcB).

sp_ProcB has several input params as well as one output parameter. For this purpose, I don't need that return variable, so I have just created a container variable within sp_ProcA to hold that value, but I am receiving errors when I try to run sp_ProcA:

Server: Msg 201, Level 16, State 3, Procedure sp_ProcB, Line 0
Procedure 'sp_ProcB' expects parameter '@OutputVariable', which was not supplied.

This seems like a common thing, I just am not sure what I am missing...

Thanks in advance for any advice.

 

[john76]

The stored procedure expects you to pass it a value for the output variable, are you doing this?

ie:

CREATE PROCEDURE sp_MyProcedure
  @MyVariable1 int,
  @MyVariable2 int OUTPUT
AS
....
GO

EXEC sp_MyProcedure 1, [COLOR=red]0[/color]
GO

Not passing it a value for the output parameter will cause the procedure to fail as you described. Dumb, I know.

HTH,
John

 

[SQLSister]

CREATE PROCEDURE sp_MyProcedure
@MyVariable1 int,
@MyVariable2 int = NULL OUTPUT

shopuld fix the problem above as it sets a default value of null to the output variable.

 

[bowline]

Thanks guys. John, I actually did have things set up correctly with the params. Sorry, guess I should have said something about being able to run ProcB by itself without a problem...

SQLSister - that did the trick. I did not realize that you could set defaults for output params. Muchas gracias!