Type conversion in EXEC statement

问题:

Imagine that we have following declaration:

declare @a int;
set @a = 1;

And there is a need to generate some informational message, such as:

select 'the value of @a is ' + @a;

The above statement will generate error, because of type conversion is required, and the right way to do it is:

select 'the value of @a is ' + convert(varchar(10), @a);

Then, if the same thing need to be done dynamically, one may expect that the following should be correct:

exec('select ''the value of @a is ' + convert(varchar(10), @a) + '''');

Surprisingly it is not, and generates syntax error. In opposite to select statement, the right way to do it in this case is:

exec('select ''the value of @a is ' + @a + '''');

So the question is, why type conversion is required in the select statement, but illegal in the exec(string) statement?

回答1:

syntax has been checked while we run any DML statement. As we know Exec is not a DML statement its give the SQL Enviornment to run any Script. exec( 'select ''the value of @a is ' + @a + '''') in exec doing implicit conversion. in this case. exec('select ''the value of @a is ' + convert(varchar(10), @a) + ''''); you are using convert function this is used in DML operation and its checking the syntax before exec.

declare @a int,@str varchar(8000)=''
set @a = 124586;

select @str='select ''the value of @a is ' + convert(varchar(10), @a) + ''''
exec( 'select ''the value of @a is ' + @a + '''')
exec(@str);