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.

Photosensitive

I believe that this is a holdover from the bad-old-days. Even back on e.g. SQL Server 2000, you could EXEC with multiple strings +ed together that would have exceeded the 8000/4000 character limits for varchar/nvarchar at the time. Therefore, I believe that the + inside an EXEC() is not the standard string concatenation operator. Although the function is only specified to accept string data types, I'd guess that means it's going to stringify any non-string types and data precedence can go walk off a short pier. But this is just speculation, hence not an answer. – Hermit

A similar question has been asked before: #26135674 – Equal

@Damien_The_Unbeliever, you have a very interesting theory, one observation though: the following does not work: EXEC( 'SELECT ' + 3 ) but concatenation with am INT variable works. – Equal

@Hermit I've read that about the character limit in Erland's article. Thanks for the insight. – Photosensitive

@Equal - yes, agreed. Worth noting though that that fails during compilation (syntax error) whereas the conversion error is a runtime error. – Hermit

@Damien_The_Unbeliever, I believe the real answer is a combination of your theory and Andrew Morton's point about implicit conversion: try the experiment with a variable of type XML, result: "Implicit conversion from data type xml to nvarchar is not allowed." – Equal

@Equal - and we can demonstrate that the + within EXEC is only working with string types with declare @i int;declare @j int;select @i=1,@j=2;exec (@i+@j) which produces "Incorrect syntax near '12'", so the original data types definitely don't matter. – Hermit

@Damien_The_Unbeliever, I saw this as well. I am guessing that all types are implicitly converted to string (NVARCHAR). It fails if no imlpicit conversion is allowed (see XML comment above). And if you specify a constant then compilation fails i.e. EXEC is not even executed. – Equal

@Equal I thought so too. As Damien pointed out earlier, all variables are converted implicitly to (N)VARCHAR. And since INT can be converted to (N)VARCHAR, there is no error produced. Unlike with the XML data type. If only there's a documentation somewhere. – Photosensitive

Perhaps there is a clue in the MSDN article for EXEC() which states that [N] 'tsql_string' Is a constant string. tsql_string can be any nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type. Perhaps a constant string is treated differently to a variable string. – Farmhouse

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.

Wellknown

Yep, the thing after the + is only allowed to be a string in the grammar so will be interpreted as a string before the concatenation. The acceptance of variables that aren't in fact any of char, varchar, nchar, or nvarchar doesn't seem to be documented though so probably should be avoided. – Basutoland

Answer 2

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

Ref: Data Type Conversion (Database Engine)

You cannot disable the 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 the call to EXEC.

I am not so sure about that. I now suspect that the argument to EXEC is passed to part of the DB engine that parses it in a different way to what we are used to seeing.

Mate

The issue isn't implicit conversion but priority. int has priority so select 'SELECT * FROM #T WHERE Val = ' + @Val throws a varchar to int conversion error. EXEC though behaves differently – Slype

@PanagiotisKanavos Nevertheless, it must be what is happening. – Mate

Answer 3

All that MSDN tell about concatinating in EXEC[UTE] is:

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

So we can not know much about what does SQL Server deep inside. All we know is that EXEC does not accept an expression as an argument, instead it accepts a list of strings delimited by '+'.

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 a number of strings or variables separated by '+' character. So you are not actually passing an expression to EXEC, and so you are bypassing the SQL Server expression parser.

Deflection

But why didn't the concatenation of int and varchar produce an error? – Photosensitive