EXEC语句中的类型转换 [英] 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) + '''');

令人惊讶的是，它并产生语法错误.与select语句相反，在这种情况下，正确的方法是:

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 + '''');

问题是，为什么在select语句中需要类型转换，而在exec(string)语句中却是非法的?

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

推荐答案

语法.众所周知，Exec不是DML语句，它为SQL环境提供了运行任何脚本的条件. exec('select''@a的值是'+ @a +'''')在exec中执行隐式转换. 在这种情况下. exec('select``@a的值为'+ convert(varchar(10)，@a)+''''); 您正在使用convert函数，该函数用于DML操作及其在exec之前的语法检查.

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);

这篇关于EXEC语句中的类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！