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!

 

[john76]

Glad I could help, I've run into this sort of issue before and I find that it's always best to explicitly define the value before assigning it to the variable.

Sometimes SQL Server acts so dumb...

John

 

[ESquared]

The behavior of data conversion in SQL server says that each different type of decimal is considered a different data type. That means decimal(10,2) is a different data type than decimal(10,3) and so on and so forth.

Implicit conversion can only be done when there is enough precision or scale to not truncate any information. It makes sense to me that SQL server refuses to implicitly convert between certain decimal data types.

-------------------------------------
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.)

 

[john76]

ESquared, that is a very good point.

OK then, if decimal/numeric data types of different precisions are considered different data types, why couldn't the SQL Server team include the precision of each type in the error message?

ie:
[red]Error converting data type decimal (20, 5) to decimal (20, 3)[/red]

or even better:
[red]Error in implicit conversion of data type decimal (20, 5) to decimal (20, 3)
The conversion must be made explicitly due to possibility of truncating data[/red]

So that we can have some sort of clue as to what the problem is?

Just my $2E-2 rant...
John

 

[ESquared]

Great idea!

-------------------------------------
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.)