How to Capture Dynamic SQL Query Results into a Variable in SQL Server?

SQL Server: Capturing Dynamic SQL Query Results in a Variable

Stored procedures often utilize dynamic SQL, building queries on the fly. The key challenge is retrieving the results of these dynamic queries within the procedure itself.

Consider this scenario:

DECLARE @sqlCommand nvarchar(1000)
DECLARE @city varchar(75)
SET @city = 'London'
SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

This code snippet dynamically generates a COUNT(*) query, but doesn't capture the result. To address this, we need a modified approach:

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @count INT
DECLARE @city VARCHAR(75)
SET @city = 'New York'

SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75), @cnt int OUTPUT', @city = @city, @cnt = @count OUTPUT

SELECT @count

Here's a detailed explanation:

1. Declare a Result Variable: We declare @cnt as an INT to hold the query's output.

2. Modify the Dynamic SQL: The SELECT statement is adjusted to assign the COUNT(*) result to @cnt: SELECT @cnt=COUNT(*).

3. Use sp_executesql with OUTPUT: sp_executesql is used with the OUTPUT parameter to specify that @cnt will receive a value from the query. Note the parameter declaration @cnt int OUTPUT.

4. Retrieve the Result: After execution, @cnt contains the count. SELECT @count displays the stored value.

This refined method efficiently captures dynamic SQL results into a variable, enabling further processing or return within the stored procedure.

The above is the detailed content of How to Capture Dynamic SQL Query Results into a Variable in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!