Why does concatenating strings in the argument of EXEC sometimes cause a syntax error in T-SQL?
- by Tim Goodman
In MS 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: http://stackoverflow.com/questions/1044831/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.
