CODEWITHSUNDEEP
sql-serverstored-procedures
TheTechnicalPaladin
TheTechnicalPaladin

Reputation: 89

Stored Procedure SELECT UPDATE Incorrect Values

I am creating a stored procedure, which I intend on running via a job every 24 hours. I am able to successfully run the procedure query but for some reason the values dont seem to make sense. See below.

This is my table and what it looks like prior to the running of the procedure, using the following statement:

SELECT HardwareAssetDailyAccumulatedDepreciationValue,
HardwareAssetAccumulatedDepreciationValue FROM HardwareAsset

enter image description here

I then run the following procedure (with the intention of basically copying the value in DailyDepreciationValue to DepreciationValue):

BEGIN

SELECT HardwareAssetID, HardwareAssetDailyAccumulatedDepreciationValue,
HardwareAssetAccumulatedDepreciationValue FROM HardwareAsset
WHERE HardwareAssetDailyAccumulatedDepreciationValue IS NOT NULL

UPDATE HardwareAsset SET HardwareAssetAccumulatedDepreciationValue = CASE WHEN
(HardwareAssetAccumulatedDepreciationValue IS NULL) THEN
CONVERT(DECIMAL(7,2),HardwareAssetDailyAccumulatedDepreciationValue) ELSE
CONVERT(DECIMAL(7,2),(HardwareAssetAccumulatedDepreciationValue + HardwareAssetDailyAccumulatedDepreciationValue))
END

END

But when i re-run the select statement the results are as follows:

enter image description here

It really doesnt make any sense to me at all any ideas?

Upvotes: 0

Views: 42

Answers (1)

Malk
Malk

Reputation: 11983

I am not able to replicate. We need more detail on the table structure and data. This is what I used to attempt to replicate. Feel free to modify as needed:

create table #t (
      AccD1 decimal(7,2)
    , AccD2 decimal(7,2)
    , AccDaily as AccD1 + AccD2
    , AccTotal decimal(7,2)
)

insert #t values 
      (100, 7.87,  null)
    , (300, 36.99, null)
    , (400, 49.32, null)
    , (100, 50.00, 100)

select * from #t

update #t set 
      AccTotal = isnull(AccTotal, 0) + AccDaily
    , AccD1 = 0
    , AccD2 = 0

select * from #t

drop table #t

Upvotes: 1

Related Questions