Error converting data type decimal to decimal.

[spence74]

I get the message above when I try to run the following stored procedure. The error occurs in the last line of the procedure.

CREATE PROCEDURE calc_SlabCullTons_Mill
(
@MillID int,
@DateFrom datetime,
@DateTo datetime,
@CullSlabs decimal(16,3) OUTPUT
)

AS

SET NOCOUNT ON

SET @CullSlabs = (SELECT CAST(SUM(Pounds) as dec(16,3)) FROM Cull C
INNER JOIN GlobalSupport2.dbo.CullCode CC ON C.CullCodeID = CC.CullCodeID
INNER JOIN GlobalSupport2.dbo.CullCategory CCC ON CC.CullCategoryID = CCC.CullCategoryID
WHERE C.MillID = @MillID AND C.DateProduction >= @DateFrom AND C.DateProduction <= @DateTo AND CCC.CullCategoryNbr = '826' )

SET @CullSlabs = CAST(ISNULL(@CullSlabs, 0) as decimal(16,3)) / 2000.0

I've tried excluding the CAST statement as well as including the entire calculation in it.

I am stumped. Any help would be greatly appreciated!

 

[SQLBill]

Based on your precision and scale, your decimals must have no more than 16 numbers and three of those must be to the right of the decimal point.

Based on the error message, you most likely have values that are 1) longer than 16 numbers or 2) have more than 3 numbers to the right of the decimal point.

Try increasing the precision and scale to 100,10. Then take the value returned and adjust the precision and scale to match that value.

-SQLBill

 

[spence74]

I tried SQLBill's suggestion (decimal values have a length limit to 38, so I could not use 100). The size is fine, but I still get the error. If the size is wrong, it will say it is an arithmatic overflow error and I did not get one of those.

We use MS SQL Server 2000.

 

[dbomrrsm]

have you tried declaring the variable as an int or numeric

[blue]DBomrrsm[/blue]

 

[SQLBill]

Can you provide a sample @CullSlabs value?

-SQLBill

 

[SQLBill]

Are you sure it's with the last line?

I ran this script:

use tempdb
go
declare @mynum decimal(16,3)
declare @mynum1 decimal(16,3)
--set @mynum = 1234567890123.123
select @mynum
select cast(isnull(@mynum,0) as decimal(16,3))/2000.0
set @mynum1 = cast(isnull(@mynum,0) as decimal(16,3))/2000.0
select @mynum1

Leaving the one line commented out created a NULL value which worked. Then I 'uncommented' the line and that worked. Then I changed the @mynum value to 0 and that worked.

Rerun your script with this line added between the two SET commands:

SELECT @CullSlabs

Let us know what value that returned.

-SQLBill

 

[spence74]

Yes, the error I recieve points to the last line. I have the select statement in my code already as part of my debugging... the value returned before it was divided by 2000.0 is 31733621.0000000000. The field being summed is an integer.

I have not tried numeric, but we do make extensive use (over 500 stored procedures and user defined functions) of the decimal data type and this is the first time that I have run into this problem.

 

[dbomrrsm]

Found this on another site - may be of help !

http://www.sqrug.org/sqr-users/sqrusers_02q3/msg00550.html

[blue]DBomrrsm[/blue]

 

[SQLBill]

I'm stuck on this one....it works just fine for me. I even put 31733621.0000000000 in place of @CullSlabs

DECLARE @CullSlabs DECIMAL(16,3)
SET @CullSlabs = CAST(ISNULL(31733621.000000000, 0) as decimal(16,3)) / 2000.0

and it worked fine.

What version of SQL Server are you using and what level of patch?

-SQLBill

 

[spence74]

I just tried the solution @ the web site listed, (declared an int to place the summed value in, then dividing the int on the final line) and I still get the error. Could there be something wrong with the destination variable?

 

[SQLBill]

Have you tried putting the actual value into the last line and running it to see what happens (like I did above)?

-SQLBill

 

[SQLBill]

One other thing to try....add this line to the DECLARE

DECLARE @CullSlabs1 DECIMAL(16,3) OUTPUT

Then change the last line to:

SET [b]@CullSlabs1[/b] = CAST(ISNULL(@CullSlabs, 0) as decimal(16,3)) / 2000.0

-SQLBill

 

[spence74]

I tried both the suggestions from SQLBill and I still get the error... this is really starting to smell like a bug in the SQL Server code.

 

[john76]

Just for curiosity's sake, try:

SET @CullSlabs = [red]CAST([/red]CAST(ISNULL(@CullSlabs, 0) as decimal(16,3)) / 2000.0 [red]AS decimal(16,3))[/red]

And see what happens.

John

 

[SQLBill]

I'm wondering if there's an ANSI setting that is causing this problem. I'll look through the documenation (BOL) and if I find anything to try I'll post it.

-SQLBill

 

[ESquared]

I second john76's answer. From other recent threads in this forum we realized that precision and scale can get larger for mathematical operations. It's likely that the conversion it's having problems with is the final implicit one done after the division by 2000 to fit the datatype of @CullSlabs.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[SQLBill]

Unless there's a typo, I think we showed it's not in the precision.

spence74 ran the first part of the code (the part that does the SUM) which worked and returned the value: 31733621.0000000000

I then put that value into the second part of the script and it worked just fine. I also suggested spence74 put the actual value into the second part of the script and he is still getting the error.

So, what's different between his SQL Server and mine? (Did either of you test it? If so, did it work or fail?).

I'm on SQL Server 2000 w/SP3. How about you Spence74?

-SQLBill

 

[spence74]

I tried john76's suggestion and it worked!! Thanks to john76 and SQLBill for all your help!

SQLBill - I'm not sure what SP(service pack?) is installed, where do I go to look?

 

[SQLBill]

Amazing, I was so sure that couldn't be the solution because you script worked for me without any errors.

Run SELECT @@VERSION. It will give you something like 8.00.766.

SQL Server 2000 is really version 8. The last three numbers tell you what 'update' you have. SP3 is 760, SP3a is 766. You can check the versions at this site:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37

If it has a YES under the PSS column, that update is only available directly from Microsoft in response to a specific problem.

-SQLBill

 

[ESquared]

SQLBill: the problem with using a literal value is that it doesn't have leading zeroes...

You don't know if 31733621.0000000000 is 0000000000000031733621.0000000000 or what.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)