从exec（@sql）返回值

Question

我想从中获取值Exec(@sql)并分配给@Rowcount(int)

这是我的查询：

'SET @RowCount = (select count(*) 
                    FROM dbo.Comm_Services 
                   WHERE CompanyId = '+cast(@CompanyId as char)+' and '+@condition+')'

Answer 1

一方面，您可以使用sp_executesql：

exec sp_executesql N'select @rowcount=count(*) from anytable', 
                    N'@rowcount int output', @rowcount output;

另一方面，您可以使用一个临时表：

declare @result table ([rowcount] int);
insert into @result ([rowcount])
exec (N'select count(*) from anytable');
declare @rowcount int = (select top (1) [rowcount] from @result);