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.
I have tried cast, conversion methods.
The + operator is overloaded in SQL Server. If any argument is numeric, then it is a string.
Often, I do what you want to do using replace() to prevent this problem:
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:
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;
Whenever you add Numerics to a string, the default behavior is to convert the string to 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.
You have to re-write this as
set @SQL_BULK='insert into '+@tb+'(ClosePrice) values('''+CAST(@cp AS VARCHAR)+''');'
