Vryjdfkk

When executing the query below if i use PRINT it prints correctly i can copy and paste the printed code and execute it. However is i use EXEC i get the following

Is there a way of simplifying what i am doing? Why does 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;