Stored Procedure Output Parameters

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)

Stored Procedure Output Parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-06 : 08:37:24

Steve writes "Here's my situation:
Windows 2000 Advanced Server
SQL Server 2000 SP3

I have (2) stored procedures, [callingProcedure] calls [infoProcedure] which needs the OUTPUT variable from [callingProcedure]

==================================================
[infoProcedure]
returns an integer value that is calculated based on using a TOP query. The integer used for the TOP selection is stored in another table, so the only way I know of running a select statement with a TOP constant is by using a "EXECUTE @strSQL" statement:

CREATE PROCEDURE [infoProcedure] @retValue int OUTPUT AS
...
DECLARE @topVal int <-- actually comes from a table
SELECT @strSQL = 'SELECT @retValue = SUM(otherVal) FROM (SELECT TOP ' + convert(varchar, @tolVal) + ' otherVal FROM otherTable)'
EXECUTE(@strSQL)
...

==================================================
[callingProcedure]
does a bunch of stuff, but the part I'm having a problem with is retrieving the @retValue OUTPUT parameter from [infoProcedure]

CREATE PROCEDURE [callingProcedure] AS
...
DECLARE @infoValue int
EXECUTE infoProcedure @infoValue OUTPUT

...The [infoProcedure] procedure does not seem to be handling the @retValue as a local variable in the procedure, which is why it is not being passed back to [callingProcedure] properly. What am I doing wrong? Is there another approach?

I hope I explained myself well enough. Thanks for any help you can offer."

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-06 : 10:34:21

Try calling the @StrSQL call like this rather than using EXECUTE() which only runs in a local context.
EXEC sp_executesql @StrSQL,N'@Retvalue INT OUTPUT',@Retvalue OUTPUT

Sarah Berger MCSD

sbrennan
Starting Member

1 Post

Posted - 2003-02-06 : 12:04:34

That worked perfectly, Sarah. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources