Using sp_ExecuteSQL 1

[790213]

I'm busy creating a stored procedure that builds up SQL strings which I then use to execute other stored procs. Now the problem is I need the return value as well as the output parameters. So the question is:

How do I set local variables = to the return parameters of stored procs when I use sp_ExecuteSQL?

Does anyone know?

 

[mimi2]

SET @sql = N'SELECT @m = MAX(update) FROM ' + @link_server_name + '.data1.dbo.' + @site + 'MAIN'

EXEC sp_executesql @sql, N'@m datetime OUTPUT', @MaxUpdate OUTPUT
hope this helps.

 

[JamesLean]

mimi2's answers shows how to assign values to variables using dynamic SQL. To assign SP return values, as per the original question, you can use this:

DECLARE @sql nvarchar(200),
  @ret int

SET @sql = N'EXEC @retval = myproc'

EXEC sp_executesql @sql, N'@retval int OUTPUT', @ret OUTPUT

--James