Error 8114 Converting numeric to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error 8114 Converting numeric to numeric

Author

Topic

zbarker
Starting Member

5 Posts

Posted - 2007-04-27 : 09:27:38

Hey guys trying to figure this one out for days now.
The error message i get is this...
Message "Error converting data type numeric to numeric."

and here is my stored procedure

//variables passed in
@CreatedBy int,
@FederalTaxRate numeric (9,8),
@FederalWithholdingAmount numeric (9,2),
@FederalWithholdingTotal numeric (9,2),
@StateTaxRate numeric (9,8),
@StateWithholdingAmount numeric (9,2),
@StateWithholdingTotal numeric (9,2)

//body of sproc
SET NOCOUNT ON

INSERT INTO tbTax (
CreatedBy,
CreatedDate,
FederalTaxRate,
FederalWithholdingAmount,
FederalWithholdingTotal,
StateTaxRate,
StateWithholdingAmount,
StateWithholdingTotal
)
VALUES (
@CreatedBy,
getDate(),
@FederalTaxRate,
@FederalWithholdingAmount,
@FederalWithholdingTotal,
@StateTaxRate,
@StateWithholdingAmount,
@StateWithholdingTotal
)

Select PK_Tax,
CreatedBy,
CreatedDate,
FederalTaxRate,
FederalWithholdingAmount,
FederalWithholdingTotal,
StateTaxRate,
StateWithholdingAmount,
StateWithholdingTotal
From tbTax
Where PK_Tax = @@IDENTITY

RETURN @@IDENTITY

not sure whats going on.

Thanks,
zbarker ~Any help is good help!

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-27 : 09:30:49

It would help us identify where the problem is if you post the DDL of your tbTax table.

SQL Server Helper
http://www.sql-server-helper.com

zbarker
Starting Member

5 Posts

Posted - 2007-04-27 : 09:41:22

sorry sshelper, but im am a novice programmer...where and how do i get the DDL from?

Thanks,
zbarker ~Any help is good help

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-27 : 09:50:34

Basically, what we need is the structure of your table.

SQL Server Helper
http://www.sql-server-helper.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 09:53:46

also show us how do you execute the stored procedure. What are the values that you pass in to the stored procedure

zbarker
Starting Member

5 Posts

Posted - 2007-04-27 : 09:56:43

CREATE TABLE [dbo].[tbTax] (
[PK_Tax] [int] IDENTITY (1, 1) NOT NULL ,
[CreatedBy] [int] NOT NULL ,
[CreatedDate] [smalldatetime] NOT NULL ,
[ModifiedBy] [int] NULL ,
[ModifiedDate] [smalldatetime] NULL ,
[FederalTaxRate] [numeric](9, 8) NULL ,
[FederalWithholdingAmount] [numeric](9, 2) NULL ,
[FederalWithholdingTotal] [numeric](9, 2) NULL ,
[StateTaxRate] [numeric](9, 8) NULL ,
[StateWithholdingAmount] [numeric](9, 2) NULL ,
[StateWithholdingTotal] [numeric](9, 2) NULL
) ON [PRIMARY]
GO

Thanks,
zbarker ~Any help is good help

zbarker
Starting Member

5 Posts

Posted - 2007-04-27 : 09:59:48

I pass in...

0, 28, 0, 0, 28, 0, 0

Thanks,
zbarker ~Any help is good help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 10:05:47

you are passing in 28 to @FederalTaxRate numeric (9,8)
the variable is to small to accommodate the value 28.

numeric(9,8) means there are total 9 digit of numbers with 8 digit of decimal places. You will need to enlarge it to like numeric(10,8)

from BOL

quote:

decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 10:07:00

and also the column size in the table


CREATE TABLE [dbo].[tbTax] (
[PK_Tax] [int] IDENTITY (1, 1) NOT NULL ,
[CreatedBy] [int] NOT NULL ,
[CreatedDate] [smalldatetime] NOT NULL ,
[ModifiedBy] [int] NULL ,
[ModifiedDate] [smalldatetime] NULL ,
[FederalTaxRate] [numeric](9, 8) NULL ,

zbarker
Starting Member

5 Posts

Posted - 2007-04-27 : 10:18:51

Thanks for your help, but one last question....if i change the columns size to say numeric(10,8) and on the this stored procedure, will any of the other stored procedures that call this table be affected by the change?

thanks,
zbarker ~Any help is good help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 10:26:32

Generally No and should not. But can't confirm anything until we have more information on the calling stored procedure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources