EXECUTING A STRING (HELP PLEASE!!)

hossrad · May 15th, 2007, 05:17 AM

Dear friends,
i have a problem here that as much as i go through it looks worth.
I have build a dynamic query with a string like below:

CREATE PROCEDURE PROC1
AS
DECLARE @TempQString nvarchar(1000)
SET @TempQString = 'DECLARE @resultvalue int'
SET @TempQString =@TempQString + 'EXEC @resultvalue=[MyStoredProcedure]'
EXEC sp_executesql @TempQString
GO

now in the body of my main stored procedure (PROC1) i want to get the
return value if the [MyStoredProcedure] that was executed through a string!!!!!!!!!!

i've tried to insert the return value in a temp table (#table)
but this also didnt work, cuz out of the string execution my temp table was dropped!! also i cant use a global temp table (##table)
because many users may execute the PROC1 at the same time!!
PLEASE HELP THIS IS VERY IMPORTANT FOR ME

God Bless you,
Hossein

melvik · May 15th, 2007, 06:26 AM

dear Hossin I donno what exatly u want but I done this like this
but remember MyStoredProcedure will not return value it'll select that
go below:;)

Code:

CREATE PROCEDURE [dbo].[MyStoredProcedure]
AS
...
SELECT 111
GO
***************************

CREATE PROCEDURE PROC1
AS
CREATE TABLE #A (ret int)

DECLARE @TempQString nvarchar(1000)
DECLARE @resultvalue int
SET @TempQString ='INSERT INTO #A EXEC [MyStoredProcedure]'
EXEC sp_executesql @TempQString
SELECT @resultvalue = Ret FROM #A
DROP TABLE #A
RETURN @resultvalue
GO

& then run this

Code:

declare @a int
EXEC @a = PROC1
SELECT @A

God bless u too, GUGUL MAGULI

HTH:D

Always:),
Hovik Melkomian.