将包含 COALESCE 的动态 sql 语句的结果分配给变量

Question

我有一个使用 COALESCE 构建的动态 SQL 语句。我需要将此动态 sql 语句的执行结果分配给一个变量。我尝试了以下代码，但它没有将输出分配给变量@result。执行完成后，exec 显示正确的结果，但"选择 @result"的输出始终为 NULL

DECLARE @SQL NVARCHAR(MAX)
DECLARE @result int  

SELECT   @SQL = COALESCE('select '+ @SQL + CHAR(13) + ' + ' + CHAR(13),'' ) 
+
'(SELECT case when count(distinct ' + convert(varchar(10),object_id) + ' ) > 0 then 1 else 0 end from sys.dm_db_index_usage_stats 
WHERE object_id = '+convert(varchar(10),object_id)
+'AND last_user_update > dateadd(minute, -1, getdate()) 
)'                         
FROM sys.dm_db_index_usage_stats
WHERE object_id in (object_id('table1'),object_id('table2'))
AND database_id = db_id(db_name())
EXEC sp_executesql @SQL, N'@result int output', @result out 
select @result

Answer 1

以下代码帮助我解决了这个问题。

DECLARE @SQL NVARCHAR(MAX)
DECLARE @result int  
DECLARE @parms nvarchar(1000)
SELECT   @SQL = COALESCE('select @cnt='+ @SQL + CHAR(13) + ' + ' + CHAR(13),'' ) 
+
'(SELECT case when count(distinct ' + convert(varchar(10),object_id) + ' ) > 0 then 1 else 0 end from sys.dm_db_index_usage_stats 
WHERE object_id = '+convert(varchar(10),object_id)
+'AND last_user_update > dateadd(minute, -1, getdate()) 
)'                          
FROM sys.dm_db_index_usage_stats
WHERE object_id in (object_id('table1'),object_id('table2'))
AND database_id = db_id(db_name())
SET @parms = N'@cnt int out'
EXEC sp_executesql @SQL, @parms, @cnt = @result out
select @result