Combining INT and VARCHAR inside EXEC without 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 the value of Val :

 DECLARE @sql NVARCHAR(MAX); DECLARE @Val INT = 3; EXEC ('SELECT * FROM #T WHERE Val = ' + @Val); 

This runs without errors and gives the correct result.

My assumption is that this will result in an error:

Conversion error when converting varchar value 'SELECT * FROM #T WHERE Val =' for int data type.

Since @Val has an INT data type and, according to the rules , a data type is a priority , the request inside EXEC must be converted to INT .

My question is: why did the EXEC call not cause a conversion error?

---

Notes:

- I know about sp_executesql . I also do not ask for alternatives. I just ask you to explain why the error was not made.

- The answer to this question does not seem to explain my situation, since the question relates to the concatenation of VARCHAR to VARCHAR .

Answer 1

According to MSDN / BOL, the simplified syntax for the 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. 

A 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 the last paragraph, SQL Server expects these variables to / have one of the following string data types: char , varchar , nchar or nvarchar .

2) In addition, this syntax refers only to string variables ( @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ). I believe that it is for this reason that EXEC ('SELECT * FROM #T WHERE Val = ' + 3); fails: 3 not a variable.

3) I assume that when one of these variables does not have one of the row types listed above, then SQL Server will do the implicit conversion. I assume that it will convert from the original variable from INT (for example) to nvarchar , because it has the highest data type between these string types.

4) This is not the only place where data type priority does not work. ISNULL(param1, param2) This is just another example. In this case, param2 will be converted to the param1 data type.

Answer 2

Implicit conversion from int to string types is allowed, at least as far back as SQL Server 2008.

Link: Data Type Conversion (Database Engine)

You cannot turn off implicit conversion: Is there a way to turn off implicit type conversion in SQL Server?

Edit: I originally wrote

'SELECT * FROM #T WHERE Val = ' + @Val is created before EXEC called.

I am not sure about that. Now I suspect that the EXEC argument is passed to parts of the database engine, which analyzes it differently than we used to see.

Answer 3

All MSDN says about concatenation in EXEC [UTE] is:

concatenation is performed logically in the SQL Server parser and is never implemented in memory.

Therefore, we cannot know much about what SQL Server does deep down. All we know is that EXEC does not accept an expression as an argument; instead, it accepts a list of strings separated by a "+" character.

If you look at the syntax:

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

It directly supports multiple lines or variables separated by a +. This way you are not actually passing the EXEC expression, and therefore you will bypass the SQL Server parser.