Sproc to Sproc OUTPUT Parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sproc to Sproc OUTPUT Parameter

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-02-03 : 13:32:31

I'm calling one SPROC from another and returning an OUTPUT parameter. This works in Query Analyzer, but the return parameter causes an error when I'm actually calling from the SPROC. When I remove the return parameter, then I can execute the Responding_Sproc from the Calling_Sproc. I've provided examples of my calling and responding SPROCs and the corresponding Query Analyzer code; which works. I'd appreciate any suggestions for getting this to work. Thanks!

Query Analyzer Code (Which Works)

EXEC Responding_Sproc @Param1, @Param2, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param2 As Param2

--I get an error indicating that there is no "ReturnParam" column in my datatable
CREATE PROCEDURE Calling_Sproc
@Param1 int

AS

DECLARE @Param2 = SELECT SomeValue FROM MyTable WHERE Criteria = @Param1

EXEC Responding_Sproc @Param1, @Param2, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param2 As Param2

CREATE PROCEDURE Responding_Sproc
@Param2 int
@ReturnParam int OUTPUT

AS

SET @ReturnParam = (SELECT MAX(ID) FROM AnotherTable) + 1
INSERT INTO AnotherTable (ID, Property) VALUES (@ReturnParam, @Param2)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 13:36:05

CREATE PROCEDURE Responding_Sproc
@Param2 int
@ReturnParam int OUTPUT

EXEC Responding_Sproc @Param1, @Param2, @ReturnParam OUTPUT

It's defined with 2 parameters but called with 3,

suspect you want
EXEC Responding_Sproc @Param2, @ReturnParam OUTPUT

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-02-03 : 13:41:33

Not sure how it works either way as your procedure doesn't show a @PARAM1 parameter at all but you are calling it with param1. So either you didn't actually paste the real stored procedure, or your just calling it incorrectly.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-02-03 : 14:19:36

My BAD on the syntax errors in the example. I'm just using this to determine why I'm unable to obtain the value of the return parameter in the calling Sproc. I've corrected the errors in the earlier example and would appeciate any suggestions for getting this to work. Thanks!

Query Analyzer Code (Which Works)
DECLARE @Param1 int
SET @Param1 = 12

EXEC Responding_Sproc @Param1 @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1

--I get an error indicating that there is no "ReturnParam" column in my datatable
CREATE PROCEDURE Calling_Sproc
@Param1 int

AS

EXEC Responding_Sproc @Param1, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1

CREATE PROCEDURE Responding_Sproc
@Param1 int
@ReturnParam int OUTPUT

AS

SET @ReturnParam = (SELECT MAX(ID) FROM AnotherTable) + 1
INSERT INTO AnotherTable (ID, Property) VALUES (@ReturnParam, @Param1)

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-02-03 : 14:56:14

You need a DECLARE @ReturnParam in your Calling_Sproc before you try to reference it for the exec.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-02-03 : 15:07:34

I've declared the @ReturnParam in both Query Analyzer and the Calling_Sproc, but still don't get the return value in the SPROC. Any other ideas? Thanks!

Query Analyzer Code (Which Works)
DECLARE @Param1 int
DECLARE @ReturnParam int
SET @Param1 = 12

EXEC Responding_Sproc @Param1 @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1

--I get an error indicating that there is no "ReturnParam" column in my datatable
CREATE PROCEDURE Calling_Sproc
@Param1 int

AS

DECLARE @ReturnParam int
EXEC Responding_Sproc @Param1, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1

CREATE PROCEDURE Responding_Sproc
@Param1 int
@ReturnParam int OUTPUT

AS

SET @ReturnParam = (SELECT MAX(ID) FROM AnotherTable) + 1
INSERT INTO AnotherTable (ID, Property) VALUES (@ReturnParam, @Param1)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 18:29:00

You need a comma between the two parameter declarartions in the SP.
Other than that check what you really have in the stored procs - it sounds like they aren't what you are posting.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-02-04 : 17:09:53

I've fixed that syntax error, but still get the return value only in Query Analyzer and not in the SPROC. Any other ideas? Thanks!

Query Analyzer Code (Which Works)
DECLARE @Param1 int
DECLARE @ReturnParam int
SET @Param1 = 12

EXEC Responding_Sproc @Param1 @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1

--I get an error indicating that there is no "ReturnParam" column in my datatable
CREATE PROCEDURE Calling_Sproc
@Param1 int

AS

DECLARE @ReturnParam int
EXEC Responding_Sproc @Param1, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1

CREATE PROCEDURE Responding_Sproc
@Param1 int,
@ReturnParam int OUTPUT

AS

SET @ReturnParam = (SELECT MAX(ID) FROM AnotherTable) + 1
INSERT INTO AnotherTable (ID, Property) VALUES (@ReturnParam, @Param1)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 19:24:39

You still aren't posting what you are trying to run. I suspect there is a typo somewhere.
Copy this into query analyser and it should run correctly

if object_id('Responding_Sproc') is not null
drop proc Responding_Sproc
go
CREATE PROCEDURE Responding_Sproc
@Param1 int,
@ReturnParam int OUTPUT
AS

SET @ReturnParam = (SELECT MAX(ID) FROM AnotherTable) + 1
INSERT INTO AnotherTable (ID, Property) VALUES (@ReturnParam, @Param1)
go
if object_id('Calling_Sproc') is not null
drop proc Calling_Sproc
go
CREATE PROCEDURE Calling_Sproc
@Param1 int

AS
DECLARE @ReturnParam int
EXEC Responding_Sproc @Param1, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1
go

DECLARE @Param1 int
DECLARE @ReturnParam int
SET @Param1 = 12

EXEC Responding_Sproc @Param1, @ReturnParam OUTPUT
SELECT @ReturnParam AS ReturnParam, @Param1 As Param1
go

exec Calling_Sproc 12
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-05 : 14:06:48

I would recommend that you name your parameters in SProc calls, and explicitly provide the Owner:

EXEC dbo.Responding_Sproc @Param1 = @Param1, @ReturnParam = @ReturnParam OUTPUT

"dbo." ensures that SQL Server will run the correct SProc if you accidentally have the same named Sproc owned by different User IDs, and also saves it some time NOT looking to see if there IS an Sproc owned by the currently logged in user!

It happens that your Parameters have the same name in both Sprocs, but there is no guarantee that the ordering of the parameters will remain the same in the future, and in my experience explicitly naming them can help discover common errors - parameters in the wrong order, and so on.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources