Returning a value from a dynamic SQL Statement

There have been times when I have needed to create a dynamic SQL Statement within a stored procedure.  You need to consider permissions of the process that is executing the dynamic sql statement but that will not be addressed here.  I had a need on a recent project to be able to execute a dynamic sql statement from a stored procedure and use its result within that same procedure.  Here is simple example of how you can do this.

This stored procedure accepts two integer parameters.  It converts them into a string values as it builds the SQL statement.  The key is that the data we desire to use is inserted into a temporary table within the dynamic sql statement.  This temporary table is created within this stored procedure and is accessible by the dynamic sql statement.  This allows us to query the data from the temporary table and use it later on within the stored procedure.


CREATE PROCEDURE dbo.Test
  @a int
  , @b int
AS
BEGIN
 DECLARE @return_status int
 DECLARE @sql varchar(8000)
   
 SET @sql = 'INSERT INTO #Temp(result_value)
             SELECT ' + convert(varchar(20), @a) + ' + ' + convert(varchar(20), @b);
   
 CREATE TABLE #Temp (
      result_value int null
 )

 EXEC (@sql);

 SELECT *
 FROM #Temp

 DROP TABLE #temp

END


This is the execution and results.

exec dbo.Test 1, 2

result_value
3
(1 row(s) affected)