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

Retrieving Results from Dynamic SQL in SQL Server Stored Procedures

Working with dynamic SQL within stored procedures often requires capturing the query's results. This can be tricky, but here's a solution for efficiently retrieving dynamic query results into a stored procedure variable.

Let's say you need to dynamically execute a query like this:

<code class="language-sql">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 VARCHAR(75)', @city = @city</code>

Direct execution won't store the result in a variable. To capture the output, modify the code as follows:

<code class="language-sql">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 VARCHAR(75), @cnt INT OUTPUT', @city = @city, @cnt = @count OUTPUT

SELECT @count</code>

This improved approach uses these key elements:

• @cnt Variable: The dynamic SQL assigns the COUNT(*) result to a local variable, @cnt.
• OUTPUT Keyword: The OUTPUT keyword designates @cnt as an output parameter.
• sp_executesql Parameters: sp_executesql now receives both the @city input parameter and the @cnt output parameter.
• Result Retrieval: Finally, SELECT @count displays the captured result. This variable now holds the count returned by the dynamic query.

This method provides a robust way to handle results from dynamic SQL within your stored procedures, ensuring accurate and efficient data retrieval.

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