Posted by Erland Sommarskog on 09/12/06 10:38

Cylix (cylix2000@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Find out the percentage complete of Stored Procedure
• Prev in forum: Find out the percentage complete of Stored Procedure
• Thread view: Re: How to get a SP return value on another SP?

[Reply to this message]