How to get a SP return value on another SP?

[Cylix]

I have a SP, which will exec other SP depend on the input.
the "other SP" need to return a integer back.

How to do this?

Thanks for give me a hand.

[SQLKen]

convert the first sp to a function that when you call it, it will
return a value.

sp can only write the value into a table and then the second sp can
pick up that value from the first sp.

Cylix wrote:

I have a SP, which will exec other SP depend on the input.
the "other SP" need to return a integer back.

How to do this?

Thanks for give me a hand.

[Erland Sommarskog]

Cylix (cy*******@gmail.com) writes:

I have a SP, which will exec other SP depend on the input.
the "other SP" need to return a integer back.

How to do this?

You could use an OUTPUT parameter for this.

CREATE PROCEDURE inner_sp @x int OUTPUT AS
SELECT @x = 23
go
CREATE PROCEDURE outer_sp AS
DECLARE @x int
EXEC inner_sp @x OUTPUT
PRINT '@x is ' + ltrim(str(@x))
The trick is that you must specify OUTPUT when you call the procedure as
well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cylix]

Thank you.

Actually, my case is a bit more complicated.
It is because, which SP is going to exec is also depends on input,
and so, in my SP, I need to
DECLARE @SQL as VARCHAR,
SET @SQL = 'exec ' + @THE_SP + ' ' + @PARM1
EXEC (@SQL)
In this structure, Can I still get the return value?
If not, any suggection?

Erland Sommarskog wrote:

You could use an OUTPUT parameter for this.

CREATE PROCEDURE inner_sp @x int OUTPUT AS
SELECT @x = 23
go
CREATE PROCEDURE outer_sp AS
DECLARE @x int
EXEC inner_sp @x OUTPUT
PRINT '@x is ' + ltrim(str(@x))

The trick is that you must specify OUTPUT when you call the procedure as
well.

[Erland Sommarskog]

Cylix (cy*******@gmail.com) writes:

Actually, my case is a bit more complicated.
It is because, which SP is going to exec is also depends on input,
and so, in my SP, I need to
DECLARE @SQL as VARCHAR,
SET @SQL = 'exec ' + @THE_SP + ' ' + @PARM1
EXEC (@SQL)
In this structure, Can I still get the return value?
If not, any suggection?

If you use sp_executesql instead of EXEC you can.

However, you don't even have to do that. You can do:

EXEC @THE_SP @PARM1 OUTPUT

That is, EXEC accepts a variable for the procedure name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx