Executing SP inside SP dynamically

I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!In Books OnLine, look up the keywords OUTPUT variable and RETURN.|||Hmmm...not sure if the OUTPUT parameters alone will do the job, as dynamic SQL operates within its own scope. Try it and see, but you can also use temp tables as a hack method to pass values across scopes.|||I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!

Try This

DECLARE @.sql nvarchar(2048)
SET @.sql = ' SET @.count = ( SELECT COUNT(*) FROM table1 )'
DECLARE @.temp int
EXEC sp_executesql @.sql, N'@.count int OUTPUT', @.temp OUTPUT

Jamessql