消息8115，级别16，状态6，行4算术溢出错误将varchar转换为数据类型numeric

This is the query I have written.

这是我写的查询。

DECLARE @SQL_BULK VARCHAR(MAX)
declare @cp decimal(14,2)=1000
declare @tb varchar(20)='tbl_LT'
set @SQL_BULK='insert into '+@tb+'(ClosePrice) values('''+@cp+''');'
EXEC (@SQL_BULK)

When I execute the query I am getting Msg 8115, Level 16, State 6, Line 4 Arithmetic overflow error converting varchar to data type numeric. as error.

当我执行查询时,我得到消息8115,级别16,状态6,行4算术溢出错误将varchar转换为数据类型数字。作为错误。

I have tried cast, conversion methods.

我试过转换,转换方法。

2 个解决方案

#1

Whenever you add Numerics to a string, the default behavior is to convert the string to Number.

每当您将Numerics添加到字符串时,默认行为是将字符串转换为Number。

In this case

在这种情况下

set @SQL_BULK='insert into '+@tb+'(ClosePrice) values('''+@cp+''');'

you are adding @cp to a string which is causing the problem.

您正在将@cp添加到导致问题的字符串。

You have to re-write this as

你必须重写这个

set @SQL_BULK='insert into '+@tb+'(ClosePrice) values('''+CAST(@cp AS VARCHAR)+''');'

#2

The + operator is overloaded in SQL Server. If any argument is numeric, then it is a string.

+运算符在SQL Server中重载。如果任何参数是数字,那么它是一个字符串。

Often, I do what you want to do using replace() to prevent this problem:

通常,我使用replace()来做你想做的事情来防止这个问题:

set @SQL_BULK = 'insert into @tb(ClosePrice) values(@cp)';
set @SQL_BULK = replace(@SQL_BULK, '@tb', @tb);
set @SQL_BULK = replace(@SQL_BULK, '@cp', @cb);
EXEC (@SQL_BULK)

As a note: you should probably use `sp_executesql and pass in the second value as a parameter:

注意:您应该使用`sp_executesql并将第二个值作为参数传递:

set @SQL_BULK = 'insert into @tb(ClosePrice) values(''@cp'')';
set @SQL_BULK = replace(@SQL_BULK, '@tb', @tb);
exec sp_executesql @SQL_BULK, N'@cb decimal(14, 2)', @cp = @cp;

#1