It's every so important to read the syntax carefully in computer science. Missing the specifics will get you every time.
Compare these two statements.
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT CustomerID FROM Customers'
EXEC @sql
to this
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT CustomerID FROM Customers'
EXEC(@sql)
I'm sure you've caught it by now. It's the parenthesis.
Syntax
Execute a stored procedure:
[ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]
[ ,...n ]
[ WITH RECOMPILE ]
Execute a character string:
EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
It's even in bold in the documentation. One would think it might jump out and grab my attention. However sadly, it did not.