BUG: Incorrect Results are Returned by Stored Procedures when You Use the Decimal Datatype (282042)

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.

MORE INFORMATION

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

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

Run the stored procedure:
```
EXECUTE myProc
					
```

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