EXECUTING A STRING (URGENT HELP PLEASE)

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:
[blue]
CREATE PROCEDURE PROC1
AS
DECLARE @.TempQString nvarchar(1000)
SET @.TempQString = 'DECLARE @.resultvalue int'
SET @.TempQString =@.TempQString + 'EXEC @.resultvalue=[MyStoredProcedure]'
EXEC sp_executesql @.TempQString
GO
[/blue]
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!!
[red]PLEASE HELP THIS IS VERY IMPORTANT FOR ME[/red]

Firstly I have to question why you are execution your stored procedures in this way as it doesn't seem to be particularly efficient. Is there another way you can perform the task?

If you still need to use this method then try the example below.

Chris

Code Snippet

CREATE PROCEDURE PROC1

AS

DECLARE @.TempQString nvarchar(1000)

DECLARE @.resultvalue INT

SET @.TempQString = 'EXEC @.resultvalue=[MyStoredProcedure]'

EXEC sp_executesql @.TempQString, @.Parameters = N'@.resultvalue int output', @.resultvalue = @.resultvalue OUTPUT

RETURN ISNULL(@.resultvalue, -1)

GO

sql