How Can I Return Dynamic SQL Results to Variables in SQL Server?

Storing Dynamic SQL Query Results in SQL Server Variables

Working with dynamic SQL in SQL Server often requires capturing query results into variables for subsequent processing. This is especially helpful when the dynamic query returns a single value.

Consider a scenario where a dynamic SQL query counts customers in a specific city. The challenge is efficiently retrieving this count and assigning it to a variable within a stored procedure.

The sp_executesql system stored procedure, used with an output parameter, provides a solution. This allows direct assignment of the dynamic SQL query's result to a variable.

Here's an improved code example demonstrating how to store the count in the @count variable:

<code class="language-sql">DECLARE @sqlCommand NVARCHAR(1000);
DECLARE @count INT;
DECLARE @city VARCHAR(75);
SET @city = 'New York';

SET @sqlCommand = N'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 revised code sets the @cnt variable within the dynamic SQL to the COUNT(*) result. sp_executesql executes the query, using output parameters @city and @cnt. The result is then neatly stored in the @count variable.

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