How to Retrieve Results from Dynamic SQL in SQL Server Stored Procedures?

Retrieve dynamic SQL results from SQL Server stored procedures

In a stored procedure, after executing dynamic SQL, the results usually need to be retrieved for further processing. An example is determining the number of records produced by a dynamic SQL query.

Execute dynamic SQL

In the provided code snippet, use sp_executesql to execute dynamic SQL to determine the number of customers in a specific city. Dynamic SQL is represented by @sqlCommand and accepts parameters @city.

Search results

In order to retrieve the count value as the return value of the stored procedure, the following steps are taken:

1. Declare a new inttype parameter@cnt to hold the count value.
2. Modify the dynamic SQL statement and set the output variable @cnt to the COUNT(*) value.
3. Call sp_executesql using modified dynamic SQL and specify @cnt as the output parameter in the parameter list.

Sample code

<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>

The above is the detailed content of How to Retrieve Results from Dynamic SQL in SQL Server Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!