sql - 将数据类型 varchar 转换为数字时出错并溢出

Question

declare @Commission decimal(18,2)
select @Commission=percentage from Commission
declare @qry varchar(Max)

set @qry='select 5 +'+@Commission +''

EXEC(@qry)

这里

将数据类型 varchar 转换为数字时出错。

标签： sqlsql-server

Answer 1

不要将值作为字符串传递给动态 SQL。相反，学习使用sp_executesql：

declare @Commission decimal(18, 2);

select @Commission = percentage
from Commission;

declare @qry varchar(Max);

set @qry='select 5 + @Commission';

exec sp_executesql @qry, 'N@Commission decimal(18, 2)', @Commission=@Commission;