将动态 SQL 的结果放入 sql-server 的变量中

在存储过程中执行动态SQL如下:

Executing dynamic SQL as follows in Stored Procedure:

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

如何在 SP 中使用 count(*) 列值作为返回值?

How do I use the count(*) column value as return value in the SP?

推荐答案

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

相关文章