Microsoft KB Archive/282042

= BUG: Incorrect Results are Returned by Stored Procedures when You Use the Decimal Datatype =

Article ID: 282042

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q282042



BUG #: 18998 (SQLBUG_65)



SYMPTOMS
Incorrect values may be inserted into a field when you update a decimal field in a table with a value from another decimal field, in a different table in which the decimal field has a different precision and scale.

This behavior is seen when the update occurs in the context of a stored procedure.



WORKAROUND
Use any of the methods that follow to work around this behavior:  Declare the datatypes of the decimal columns in the two tables with the same precision and scale.

-or-  Use a variable to input the value into the second table as shown in this code: CREATE PROCEDURE myProc as DECLARE @var decimal(9,2) SELECT @var = col2 from myTab1 where col1=1 UPDATE myTab2 set col2 = @var SELECT * FROM myTab2 go -or-  Run the same code outside of the context of a stored procedure.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.

This is not an issue in SQL Server 7.0 and later.



Steps to Reproduce Behavior
  Create a table and insert values: CREATE TABLE myTab1( col1 int identity(1,1), col2 decimal(8,2)) go CREATE TABLE myTab2(  col2 decimal(9,2)) go INSERT INTO myTab1 VALUES(11.33) go INSERT INTO myTab2 VALUES(12.56) go </li>  Create the stored procedure that performs the update: if exists (select name from sysobjects where name = 'myProc' and type = 'P') drop procedure p1 go

CREATE PROCEDURE myProc as UPDATE myTab2 set col2 = (select col2 from myTab1 where col1=1) SELECT * FROM myTab2 go </li>  Run the stored procedure: EXECUTE myProc </li></ol>

The stored procedure displays 1133.0000 instead of 11.33 as the value for col2 in the myTab2 table.

Keywords: kbbug kbpending KB282042

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.