Store the dynamic SQL script result in the variable (Get sp_executesql result into a variable)

In complex query we are using some dynamic SQL script. To execute the Dynamic Sql script we are using Sp_executesql command. Some time we need to store the result of the executed SQL script and we are using this value on the other place.

Let’s see the example.

Suppose we want to store the single value we use the Output clause.

In the below example we are setting the current date. 

DECLARE @SQL_Query NVARCHAR(4000)        ,@Date NVARCHAR(1000) SET @SQL_Query = N'SELECT @Date =  FORMAT (getdate(), ''dd/MM/yyyy '') ' EXEC SP_EXECUTESQL @SQL_Query, N'@Date NVARCHAR(1000) OUTPUT', @Date = @Date OUTPUT SELECT @Date AS Today_Date

Below is the output.  

If we want to store the data in the date in this case, we need to create the Temp table or variable table to store the data.

Let’s see the example.

In this example we are pulling the data from the table (Address here we are using AdventureWorks2019) and storing the data into the temp table.

CREATE TABLE #Address(     AddressID int,                 AddressLine1 nvarchar (60) NOT NULL,                 AddressLine2  nvarchar (60) NULL,                 City  nvarchar (30) NOT NULL,                 StateProvinceID int NOT NULL,                 PostalCode  nvarchar (15) NOT NULL,                 SpatialLocation geography NULL                 )  DECLARE @SQL_Query NVARCHAR(4000)  set @SQL_Query='SELECT AddressID       ,AddressLine1       ,AddressLine2       ,City       ,StateProvinceID       ,PostalCode       ,SpatialLocation FROM Person.Address'  INSERT INTO #Address EXEC SP_EXECUTESQL @SQL_Query  SELECT * FROM #Address  Drop table #Address

 See the result.