Title

How to return a value from dynamicaly executed T-SQL code
  
Summary

You can return value from a dynamicaly executed T-SQL into a local variable using undocumented in SQL Server Books Online (it's documented in mskb #262499) feature of system extended stored procedure sp_executesql. This method is shorter and simplier than the common practice of using a temporary table.
  
Description

If you try to return value from dynamicaly executed T-SQL you'll quickly find out that the calling procedure local variables are not within scope of dynamic SQL because it is treated as another batch. The common practice to overcome that limitation involves writing the value to a temporary table and then pulling it out in the calling procedure. Listing #1 illustrates this


-- Listing #1
DECLARE @ret_ext_max nvarchar(100), @SQLstr nvarchar(100)
CREATE TABLE #returnvalue (retval nvarchar(100))
SET @SQLstr = N'SELECT MAX(au_id) FROM pubs..authors'
INSERT INTO #returnvalue EXECUTE (@SQLstr)
SET @ret_ext_max = ( SELECT retval FROM #returnvalue)
PRINT @ret_ext_max
The sp_executesql system stored procedure allows output parameters to be specified so that any output generated from the T-SQL statements can be stored (although this is not documented in SQL Server Books Online). Based on that code above could be rewriten as shown in the Listing #2.


-- Listing #2
DECLARE @ret_ext_max nvarchar(100), @SQLstr nvarchar(100)
SET @SQLstr = N'SELECT @ret_ext_max = MAX(au_id) FROM pubs..authors'
EXEC sp_executesql @SQLSTR, N'@ret_ext_max nvarchar(100) OUTPUT',
            @ret_ext_max OUTPUT
PRINT @ret_ext_max
As you can see this code is much shorter and works without temporay table.
The Listing #3 shows how to return more than one value and at the same time pass parameter to the dynamic sql. Notice, that ouput parameters listed first because you have to use only named parameters after the first named parameter has been used.


-- Listing #3
DECLARE @ret_ext_max nvarchar(100), @ret_ext_min nvarchar(100)
DECLARE @SQLstr nvarchar(100)

SET @SQLstr = N'SELECT @ret_ext_min = MIN(au_id), @ret_ext_max = MAX(au_id)' +
      ' FROM pubs..authors Where State = @State'
EXEC sp_executesql @SQLSTR,
  N'@ret_ext_min nvarchar(100) OUTPUT, @ret_ext_max nvarchar(100) OUTPUT, @State char(2)',
  @ret_ext_min OUTPUT, @ret_ext_max OUTPUT, @State = 'CA'
PRINT @ret_ext_min
PRINT @ret_ext_max
If for same reason you want to use only named parameter than see Listing #4

-- Listing #4
DECLARE @ret_ext_max nvarchar(100), @ret_ext_min nvarchar(100)
DECLARE @SQLstr nvarchar(100)

SET @SQLstr = N'SELECT @ret_int_min = MIN(au_id), @ret_int_max = MAX(au_id)' +
      ' FROM pubs..authors Where State = @State'
EXEC sp_executesql @SQLSTR,
  N'@State char(2), @ret_int_min nvarchar(100) OUTPUT, @ret_int_max nvarchar(100) OUTPUT',
  @ret_int_min=@ret_ext_min OUTPUT, @ret_int_max=@ret_ext_max OUTPUT, @State = 'CA'
PRINT @ret_ext_min
PRINT @ret_ext_max