Concatenating INT and VARCHAR inside EXEC not producing conversion error

Question

Given the following table:

USE tempdb;

CREATE TABLE #T(Val INT);
INSERT INTO #T VALUES (1), (2), (3), (4), (5);

I wanted to execute a dynamic sql query using EXEC given a Val value:

DECLARE @sql NVARCHAR(MAX);
DECLARE @Val INT = 3;

EXEC ('SELECT * FROM #T WHERE Val = ' + @Val);

This executes without error and gives the correct result.

My assumption is that this will produce an error:

Conversion failed when converting the varchar value 'SELECT * FROM #T WHERE Val = ' to data type int.

Since @Val is of INT data type and by the rules of the data type precedence, the query inside the EXEC must be converted to INT.

My question is why didn't the call to EXEC produce a conversion error?

---

Notes:

- I know about sp_executesql. I'm not also asking for an alternative. I'm just asking for an explanation why no error was produced.

- The answer to this question does not seem to explain my situation as the question refers to VARCHAR to VARCHAR concatenation.

Answer 1

According to MSDN/BOL, simplified syntax for EXEC[UTE] statement is:

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

@string_variable
Is the name of a local variable. @string_variable can be any char, varchar, nchar, or nvarchar data type. These include the (max) data types.

Few notes:

1) According to this line ( { @string_variable | [ N ] 'command_string [ ? ]' } [ **+** ...n ], we can write something like this EXEC (@var1 + @var2 + @var3) but according to last paragraph SQL Server expects these variables to be/have one of following string data type: char, varchar, nchar, or nvarchar.

2) Also, this syntax references only string variables (@string_variable | [ N ] 'command_string [ ? ]' } [ + ...n). I believe that this is the reason why EXEC ('SELECT * FROM #T WHERE Val = ' + 3); fails: 3 isn't a variable.

3) I assume that when one of these variables don't have one of above string types then SQL Server will do an implicit conversion. I assume it will convert from source variable from INT (for example) to NVARCHAR because it has the highest data type precedence between these string types.

4) This is not the only place where data type precedence doesn't work. ISNULL(param1, param2) it's just another example. In this case, param2 will be converted to data type of param1.