通过 T-SQL 将 Count(*) 传递给变量

Question

如何将 Count(*( 传递给@FoundFields变量？请注意，OECompany 表中有 2 条记录满足此标准，但@FoundFields的输出为空 ：-(

--******************
declare @sql nvarchar(4000)
DECLARE @MyTable nvarchar(50);
DECLARE @SecondaryTargetField nvarchar(100)
DECLARE @FoundFields int
set @sql = N'
select 
@FoundFields=(count(*))
from 
sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where 
c.name LIKE @SecondaryTargetField 
and t.name= @MyTable'
SET @MyTable='OECompany'
SET @SecondaryTargetField='%PRODUCTGROUP%'
PRINT @sql
EXEC sp_executesql @sql, N' @FoundFields int OUTPUT, @SecondaryTargetField VARCHAR(100), @MyTable nvarchar(50)',
@FoundFields, @SecondaryTargetField, @MyTable  
PRINT @FoundFields

Answer 1

将 OUTPUT 关键字添加到要从查询返回的字段中。

EXEC sp_executesql @sql, N' @FoundFields int OUTPUT, @SecondaryTargetField VARCHAR(100), @MyTable nvarchar(50)',
@FoundFields OUTPUT,
@SecondaryTargetField,
@MyTable