sql-server-2008 - 动态查询返回参数很难

Question

我有以下 SQL:

DECLARE @SQL NVARCHAR(500)
DECLARE @Count INT

SET @SQL = 'SELECT @Count = COUNT(*) FROM customers WHERE lastname = ''alex'''

print @sql
EXECUTE sp_executesql @SQL, N'@Count int OUTPUT'
SELECT @Count

当我运行它时，出现以下错误。知道我做错了什么吗？

Msg 8178, Level 16, State 1, Line 1
The parameterized query '(@Count int OUTPUT)SELECT @Count = COUNT(*) FROM customers WHERE ' expects the parameter '@Count', which was not supplied.

Answer 1

您错过了供应 @Count参数作为 sp_executesql 的一部分调用(请注意，在这种情况下，它也应标记为 OUTPUT):

...

EXECUTE sp_executesql @SQL, N'@Count int OUTPUT', @Count OUTPUT
SELECT @Count

sp_executesql中的第二个参数call 是查询参数定义，或所谓的形式参数(在您的情况下有单个参数 N'@Count int OUTPUT' )。如果查询是参数化的，那么后面的(第三个和后续参数)就是所谓的实际参数。

下面的示例可以更好地解释:

-- sample query #1
-- will fail, because of query is pararmeterized,
-- and actual pararmeter is not provided
EXEC sp_executesql N'SELECT @Num', N'@Num int';

-- sample query #2
-- formal parameter is @Num of type int
-- actual parameter is literal constant 153
EXEC sp_executesql N'SELECT @Num', N'@Num int', 153;

-- sample query #3
-- formal parameter is @Num of type int
-- actual parameter is variable @p
DECLARE @p int;
SET @p = 1;
EXEC sp_executesql N'SELECT @Num', N'@Num int', @p;

-- sample query #4
DECLARE @p int;
SET @p = 15;
EXEC sp_executesql N'SELECT @Num=@Num*2', N'@Num int output', @p output
select @p;