Can’t run a function against EXEC My SQL Server book says you can't run a function inside EXEC and shows an example:

DECLARE @NumberOfLetters int = 15;
EXEC(‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar)
 + ‘) AS ShortName FROM Production.Product’);


This doesn't work as the CAST() function needs to be fully resolved prior to the EXEC line, so we can modify the code as:

DECLARE @NumberOfLetters AS int = 15;
DECLARE @str AS varchar(255);
SET @str = ‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar) + ‘) AS ShortName FROM Production.Product’;
EXEC(@str);


This time it works because, as mentioned above, the CAST() function needs to be fully resolved prior to the EXEC line.

But I have one more question, what about the LEFT() function, why it can be run against EXEC?
The LEFT() function in your example is not part of the EXEC statement. It is inside the string representing part of the dynamic SQL to be executed by your EXEC statement.

In contrast, the CAST() function is used to build that string. If you use the correct quotation marks – ' instead of ‘ &amp; ’ – then perhaps you can also see the difference in the syntax highlighting:

DECLARE @NumberOfLetters int = 15;
EXEC('SELECT LEFT(Name,' + CAST(@NumberOfLetters AS varchar)
 + ') AS ShortName FROM Production.Product');


If syntax highlighting shows correctly on your monitor, then you should be able to see that the LEFT keyword is shown in red as part of a string, whereas the name CAST is shown in a different colour, and that is because it is not part of a string, rather it is part of an expression used to construct a string. That is precisely what the problem is: EXEC (...) does not accept an expression. It only accepts either a (single) string literal or a string variable.

Because you have to build your dynamic SQL script based on some values (the @NumberOfLetters value, in this case), you have to use a variable. You store the built script in it and then you pass it to the EXEC statement to run the dynamic script. That is what the modified code does:

DECLARE @NumberOfLetters AS int = 15;
DECLARE @str AS varchar(255);

-- you build the dynamic script first and store it in a variable
SET @str = 'SELECT LEFT(Name,' + CAST(@NumberOfLetters AS varchar)
         + ') AS ShortName FROM Production.Product';

-- then you just pass the variable to EXEC to run the dynamic script
EXEC(@str);