[SOLVED] Why does concatenating strings in the argument of EXEC sometimes cause a syntax error in T-SQL?

Why does concatenating strings in the argument of EXEC sometimes cause a syntax error in T-SQL?

In SQL Server Management Studio 2005, running this code

EXEC('SELECT * FROM employees WHERE employeeID = ' + CAST(3 AS VARCHAR))

gives this error:

Incorrect syntax near 'CAST'

However, if I do this, it works:

DECLARE @temp VARCHAR(4000)
SET @temp = 'SELECT * FROM employees WHERE employeeID = ' + CAST(3 AS VARCHAR)
EXEC(@temp)

I found an explanation here: T-SQL: Cannot pass concatenated string as argument to stored procedure

According to the accepted answer, EXEC can take a local variable or a value as its argument, but not an expression.

However, if that's the case, why does this work:

DECLARE @temp VARCHAR(4000)
SET @temp = CAST(3 AS VARCHAR)
EXEC('SELECT * FROM employees WHERE employeeID = ' + @temp)

'SELECT * FROM employees WHERE employeeID = ' + @temp sure looks like an expression to me, but the code executes with no errors.

Solution

The documentation states that EXEC can take either a string variable, a constant T-SQL string, or combinations/concatenations of both of them. This is demonstrated in the SQL syntax, which states the syntax is:

-- Execute a character string
{ EXEC | EXECUTE }
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS { LOGIN | USER } = ' name ' ]
[ ; ]

So a string variable, or literal string, can be used n number of times, concatenated by the concatenation operator (+). Your "why does this work" example uses a concatenation of a constant T-SQL string and a string variable, and so is perfectly legal.