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:

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

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:

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

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!