SQL 列印與 SQL 執行

執行下面的查詢時，如果我使用PRINT它會正確列印。我可以複製並粘貼列印的程式碼並執行它。但是，如果我使用 EXEC，則會收到以下錯誤：

有沒有辦法簡化我正在做的事情？為什麼 SQLPRINT和 SQLEXEC會提供這兩個截然不同的結果集？

DECLARE @TableName as NVARCHAR(250), @SQL as VARCHAR(MAX);

DECLARE @TableCursor as CURSOR;
SET @TableCursor = CURSOR FOR
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
AND name like 'HISTORY_MasterList_%'
ORDER BY sobjects.name

OPEN @TableCursor;
FETCH NEXT FROM @TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='select '''+ @TableName +''', 0
Union All
select All ''Server Count'',count(1) from ['+ @TableName +'] 
Union All
select All ''Server Cores'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +'] 
Union All
select ''Production Servers'',count(1) from ['+ @TableName +'] where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
Union All
select ''Production Cores'', sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
Union All
select ''Production Server Count after filtering out passive/failover servers'', count(1) from 
(select  distinct m.ServerName
      from ['+ @TableName +']  m
      inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
      where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
      and unit <> 0) aa
Union All
select ''Production Server Cores after filtering out passive/failover servers'', sum(convert(decimal(18,0),cores)) from(
      select  distinct m.ServerName, m.Cores
      from ['+ @TableName +']  m
      inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
      where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
      and unit <> 0) aa
Union All
select ''Non-Prod SQL Instances downgraded to Developer Edition'',count(1) from ['+ @TableName +'] where ''InstanceStatus'' like ''Downgrade%''
Union All
select ''Non-Prod SQL Instance Core Count downgraded to Developer Edition'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']  where ''InstanceStatus'' like ''Downgrade%''
Union All
select ''Non-Prod VMs moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''VM Moved/right sized from Prod Env to NonProd''
Union All
select ''Non-Prod VMs TO BE moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''Sent for V2V - non prod split'' 
'

EXEC @SQL

FETCH NEXT FROM @TableCursor INTO @TableName;
END
CLOSE @TableCursor;
DEALLOCATE @TableCursor;

錯誤是這樣說的...it is not a valid identifier。當您傳遞EXEC @SQL時，它期望它@sql代表儲存過程（或標量函式）的名稱。因此錯誤消息：...it is not a valid identifier.

如果你想執行一個文本，動態傳遞，你應該把那個文本封裝在裡面( )，比如EXEC (@SQL).

引用自：https://dba.stackexchange.com/questions/232492