EXECUTING A STRING (URGENT HELP PLEASE)

• Hossein Rad

  SSC Journeyman

  Points: 84

  More actions

  May 15, 2007 at 4:37 am

  #195730

  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!!

  [red]PLEASE HELP THIS IS VERY IMPORTANT FOR ME[/red]

• Jan-155192

  SSCrazy

  Points: 2613

  More actions

  May 15, 2007 at 6:00 am

  #706447

  Hossein,

  sp_executesql supports output parameters, see BOL.

  Jan

• Hossein Rad

  SSC Journeyman

  Points: 84

  More actions

  May 15, 2007 at 6:13 am

  #706450

  Thanks for your Help Jan,

  Wish best Stored Procedures For You,

  Hossein.