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.  

Sql Script to get the server information

 Below script is use to get the SQL Server information.

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion, CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel, CONVERT(CHAR(100),SERVERPROPERTY('ResourceLastUpdateDateTime'))AS ResourceLastUpdateDateTime, CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion, CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security' WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security' END AS IsIntegratedSecurityOnly, CASE WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition' WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition' WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise Edition' WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition' END AS EngineEdition, CONVERT(CHAR(100), SERVERPROPERTY('InstanceName')) AS InstanceName, CONVERT(CHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS ComputerNamePhysicalNetBIOS, CONVERT(CHAR(100), SERVERPROPERTY('LicenseType')) AS LicenseType, CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses')) AS NumLicenses, CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion'))AS BuildClrVersion, CONVERT(CHAR(100), SERVERPROPERTY('Collation')) AS Collation, CONVERT(CHAR(100), SERVERPROPERTY('CollationID')) AS CollationID, CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle')) AS ComparisonStyle, CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('EditionID')) = -1253826760 THEN 'Desktop Edition' WHEN SERVERPROPERTY('EditionID') = -1592396055 THEN 'Express Edition' WHEN SERVERPROPERTY('EditionID') = -1534726760 THEN 'Standard Edition' WHEN SERVERPROPERTY('EditionID') = 1333529388 THEN 'Workgroup Edition' WHEN SERVERPROPERTY('EditionID') = 1804890536 THEN 'Enterprise Edition' WHEN SERVERPROPERTY('EditionID') = -323382091 THEN 'Personal Edition' WHEN SERVERPROPERTY('EditionID') = -2117995310 THEN 'Developer Edition' WHEN SERVERPROPERTY('EditionID') = 610778273 THEN 'Enterprise Evaluation Edition' WHEN SERVERPROPERTY('EditionID') = 1044790755 THEN 'Windows Embedded SQL' WHEN SERVERPROPERTY('EditionID') = 4161255391 THEN 'Express Edition with Advanced Services' END AS ProductEdition, CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered' WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered' WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error' END AS IsClustered, CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed' WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed' WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error' END AS IsFullTextInstalled, CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet')) AS SqlCharSet, CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName')) AS SqlCharSetName, CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder')) AS SqlSortOrderID, CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName')) AS SqlSortOrderName ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))

 Getting the below output