Insert numeric variable into SQL statement

Question

How can I insert a numeric variable in a statement from within a stored procedure (here is my attempt):

...
declare @SQL1 nvarchar(1500)
declare @calc numeric(18,2)
Set @calc = 18.00
SET @SQL1= 'insert into Table1(cv) values(' + @calc + ')'

The error I get on the last line is:

Error converting data type varchar to numeric.

If I insert a variable of type nvarchar like this:

SET @SQL1= 'insert into Table1(cn) values(''' + @column_name + ''')'

it works like a charm - but not when I try to insert a numeric field as above in the first example. Thank you!

PS: The table Table1 was correctly defined for its columns to match the variable types. Table1 has 2 columns: cn of type nvarchar(50) and cv of type numeric(18,2). @column_name is nvarchar(50) and @calc is numeric(18,2).

One query has cn as a field, the other cv. Which is right, and what are their data types? — Oded
– Oded, Commented Jan 5, 2013 at 11:45

marc_s · Accepted Answer · 2013-01-05 11:47:25Z

2

You need to cast your numeric to a nvarchar - try this:

declare @SQL1 nvarchar(1500)
declare @calc numeric(18,2)

Set @calc = 18.00

SET @SQL1 = N'insert into Table1(cv) values(' + CAST(@calc AS NVARCHAR(10)) + N')'

answered Jan 5, 2013 at 11:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

Great, worked like a charm, thank you. I had the funny feeling Sql was automatically converting to nvarchar and the statement was expecting a number to match the 'cv' column type. I'm not sure why this happens but glad to apply your solution.

Collectives™ on Stack Overflow

Insert numeric variable into SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related