Csdrhrt

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).