How Can I Capture Dynamic SQL Results into Variables in SQL Server?

Storing Dynamic SQL Results in SQL Server Variables

Dynamic SQL, frequently used within stored procedures, offers the flexibility to execute SQL statements at runtime, enabling the creation of adaptable and parameterized queries. However, capturing the results of these dynamic queries into variables presents a unique challenge.

The Challenge

Consider a stored procedure employing dynamic SQL:

<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 nvarchar(75)', @city = @city</code>

The objective is to store the COUNT(*) result in a variable. Directly accessing this result is not straightforward.

The Solution: OUTPUT Parameters

The solution lies in utilizing OUTPUT parameters with dynamic SQL. Here's a refined version of the stored procedure:

<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 nvarchar(75), @cnt int OUTPUT', @city = @city, @cnt = @count OUTPUT

SELECT @count</code>

This improved approach incorporates these key changes:

1. A new variable, @count, is declared to hold the query's result.
2. The dynamic SQL statement now assigns the COUNT(*) to the parameter @cnt.
3. sp_executesql is called, explicitly defining @cnt as an OUTPUT parameter.
4. Finally, the value of @count is selected, revealing the retrieved result.

This method effectively captures dynamic SQL results into variables, making stored procedures suitable for intricate data retrieval tasks.

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