August 8, 2006 at 10:31 am
I have got some code that a colleague has written and I would like to get some feedback as to whether the use of the variables in this way is a legitimate use of variables in an update command or are there hidden dangers or any other comments you would make
CREATE TABLE [dbo].[tblRequirements] (
[RequirementsId] [int] IDENTITY (1, 1) NOT NULL ,
[ProductId] [int] NOT NULL ,
[Quantity] [decimal](14, 4) NOT NULL ,
[QuantityAllocated] [decimal](18, 0) NOT NULL ,
[RequirementDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
With Records
1, 1, 29.0000, 0, 2006-01-01
2, 1, 174.0000, 0, 2006-01-02
3, 1, 29.0000, 0, 2006-01-03
4, 1, 95.0000, 0, 2006-01-04
5, 1, 468.0000, 0, 2006-01-05
The code allocates the @QtyToAllocate over the records in date order. The code to specifically look at is that highlighted in red
Declare
@QtyToAllocate Decimal(14,4),
@QtyRemaining Decimal(14,4),
@CopyQtyToAllocate Decimal (14,4)
Declare
@Requirements Table
( RequirementsId Int Identity (1,1),
Quantity Decimal (14,4),
QuantityAllocated Decimal(14,4))
Set @QtyToAllocate = 1000
Insert Into
@Requirements
(Quantity, QuantityAllocated)
Select
Quantity, 0
from
dbo.tblRequirements
Where
ProductId = 1
Order By
RequirementDate
Update
@Requirements
Set
@CopyQtyToAllocate = @QtyToAllocate,
QuantityAllocated = Case When Quantity < @CopyQtyToAllocate Then Quantity Else @CopyQtyToAllocate End,
@QtyToAllocate = Case When @QtyToAllocate - Quantity < 0 Then 0 Else @QtyToAllocate - Quantity End
Update
A
Set
QuantityAllocated = B.QuantityAllocated
From
dbo.tblRequirements A
Join
@Requirements B On A.RequirementsId = B.RequirementsId
Where
A.QuantityAllocated <> B.QuantityAllocated
August 8, 2006 at 1:16 pm
It looks to me like your colleague has found a set based method to his UPDATE where most people would have created a cursor. While his code may be a bit harder to read, it will blow the pants off of a cursor UPDATE any day in terms of performance.
August 9, 2006 at 12:20 am
The issue I see is the use of the RequirementsId Int Identity (1,1) column in the temprorary table @Requirements and then later using it to join with the Requirements.RequirementsId. In the real world I doubt if you will ever again get the temp table's ID match.
I suspect that this results in updating the wrong Requirements table rows.
It would help if you also provided the desired desired results after the update.
Andy
August 9, 2006 at 1:49 am
Yes David I see your point I think when I simplified the code I should have put another Id in the tblRequirements table which would be the Id from the original table i.e.
Declare
@Requirements Table
( RequirementsId Int Identity (1,1),
OriginalId Int,
Quantity Decimal (14,4),
QuantityAllocated Decimal(14,4))
Insert Into
@Requirements
(OriginalId, Quantity, QuantityAllocated)
Select
RequirementsId, Quantity, 0
from
dbo.tblRequirements
Where
ProductId = 1
Order By
RequirementDate
Update
A
Set
QuantityAllocated = B.QuantityAllocated
From
dbo.tblRequirements A
Join
@Requirements B On A.RequirementsId = B.OriginalId
Where
A.QuantityAllocated <> B.QuantityAllocated
The outcome of the update if the @AmountToAllocate = 250 is
1, 1, 29.0000, 29, 2006-01-01
2, 1, 174.0000, 174, 2006-01-02
3, 1, 29.0000, 29, 2006-01-03
4, 1, 95.0000, 18, 2006-01-04
5, 1, 468.0000, 0, 2006-01-05
250 allocate agains the records by date
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply