Can I use dynamic SQL in a stored procedure to call another stored

Hi,
I am having one stored procedure named SP1 which is having one input and one
output parameter as follows
CREATE PROCEDURE dbo.SP1
@.xmldoc TEXT
,@.FLAG_EXEC VARCHAR(5)
AS
BEGIN
...
END
I have write another stored procedure named SP2 which is creating a dynamic
SQL statement like
CREATE PROCEDURE dbo.SP2
AS
BEGIN
DECLARE @.CURR_PROC_NAME VARCHAR(125)
DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
DECLARE @.STR_RET_QRY_MN VARCHAR(125)
DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
@.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE'' '
print @.STR_EXECUTE_QRY
EXEC (@.STR_EXECUTE_QRY)
END
but it always says to declare @.STR_RETURN_STRING variable. Can I do like
above? If yes then how? If no then why?
With thanx in advance,Hi Rajendra,
this does not work because each EXEC is running in it's own process.
That means in fact that proc2 does not know anything about the variables
you have declared in proc1.
You should handle it as follows:
Proc2 (Sub-Proc) writes the ReturnValue into a table
Proc2 (Calling Proc) reads after the run of Proc2 the value from the table
Another way is to work with "sp_executesql". See BOL for details...
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Rajendra (Rajendra@.discussions.microsoft.com) writes:
> I have write another stored procedure named SP2 which is creating a
> dynamic SQL statement like
> CREATE PROCEDURE dbo.SP2
> AS
> BEGIN
> DECLARE @.CURR_PROC_NAME VARCHAR(125)
> DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
> DECLARE @.STR_RET_QRY_MN VARCHAR(125)
> DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
> SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
> @.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE''
'
> print @.STR_EXECUTE_QRY
> EXEC (@.STR_EXECUTE_QRY)
> END
> but it always says to declare @.STR_RETURN_STRING variable. Can I do like
> above? If yes then how? If no then why?
You are making it too complicated. Just say:
EXEC @.CURR_PROC_NAME @.STR_XML_AS_VARCHAR, STR_RET_QRY_MN OUTPUT,
@.FLAG_EXEC = 'FALSE'
This will execute the procedure of which the name is in @.CURR_PROC_NAME.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx