Error converting data type numeric to int

Question

I have two databases on the same SQL Server. Same code.

I have a stored procedure with an input parameter @Test INT. In one database when I pass a NUMERIC value into that input, it gets truncated and everything works fine. In the other one, when doing the same thing, I get the following error:

Error converting data type numeric to int

Here is the stored procedure signature:

CREATE PROCEDURE [dbo].[blProductGetTariff]
(
    @CurrencyID             INT,
    @ProductID              INT,
    @TradeDate              DATETIME,
    @TotalDays              INT,
    @onTariff               NUMERIC(22, 10) OUTPUT
)

And here's how I invoke that stored procedure:

EXEC blProductGetTariff @CurrencyID, 
                        @MTPLY_VolumeID, 
                        @TradeDate, 
                        @VolumeParam, 
                        @VolumeMultiplierRate OUTPUT

@VolumeParam is numeric and it gets fed fine into @TotalDays in one db, but not in the second one.

What would be the possible database setting responsible for this, and is there one? Or I'm missing something, and there are other reasons for this weird behaviour?

UPDATE: Darin made a very good point in one of his comments below: "Also, looking at your code above, you might be overflowing the int if you are passing a numeric(22,10). In which case you have to make the numeric smaller or pass a bigint".. He was right. So changing INT to BIGINT resolved the problem, although I still do not understand why it does not happen in the first database, where everything works fine with INT and I use exactly the same data(actually the same file) for testing.

Please give some code that works fine in one DB but not in the other. — Martin Smith
– Martin Smith, Commented Feb 20, 2013 at 17:08
Please check that arithabort and arithignore are also the same on both databases (per Darin below). And for that matter that the compatability level is the same. — Kenneth Fisher
– Kenneth Fisher, Commented Feb 20, 2013 at 18:01

darin · Accepted Answer · 2013-02-20 17:32:44Z

4

I think you are dealing with a database wide setting for NUMERIC_ROUNDABORT, arithabort or arithignore. You can read more here:

arithignore http://msdn.microsoft.com/en-us/library/ms184341.aspx

arithabort http://msdn.microsoft.com/en-us/library/ms190306.aspx

NUMERIC_ROUNDABORT http://msdn.microsoft.com/en-us/library/ms188791.aspx

I think doing this before your query will ignore your error. This might not be what you want to do in the long run though. Fixing the proc is probably best.

SET NUMERIC_ROUNDABORT OFF

The databases most likely have different values for NUMERIC_ROUNDABORT. Right click the database in SSMS and click properties. Go to options and you will find this setting under Miscellaneous.

edited Feb 20, 2013 at 17:32

answered Feb 20, 2013 at 17:17

darin

4983 silver badges6 bronze badges

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

5 Comments

IgorShch Over a year ago

Thank you for the reply, Darin.. I don't think this is it though, because both databases have the same NUMERIC_ROUNDABORT setting.

darin Over a year ago

Try putting SET NUMERIC_ROUNDABORT OFF above your query and see if that fails.

darin Over a year ago

Have you tried running something like this in SSMS? You'll have to replace the #'s with @'s. declare #numeric numeric(9,2), #int int set #numeric = 50.23 set #int = #numeric select #int If that works you could just declare another variable and do the conversion before you pass it to the stored proc.

darin Over a year ago

Also, looking at your code above, you might be overflowing the int if you are passing a numeric(22,10). In which case you have to make the numeric smaller or pass a bigint.

IgorShch Over a year ago

Thank you very much, Darin. I changed INT to BIGINT and everything started working in the database where I had problems before. It's weird because I used exactly the same data to test it in two databases and debuging proved it again. Is there a reason for that? Thank you very much for your help, really appreciate it.

Collectives™ on Stack Overflow

Error converting data type numeric to int

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related