Fdtxjr

When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error:

Is there a way of simplifying what I am doing? Why do SQL PRINT and SQL EXEC deliver these two very different result sets?

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;

The error is saying that ...it is not a valid identifier.
When you pass EXEC @SQL , it is expecting that @sql to represent the name of a stored procedure (or scalar function). Hence the error message: ...it is not a valid identifier.

If you want to execute a text, to pass dynamically , you should encapsulate that text inside ( ), like EXEC (@SQL).