· 8 years ago · Apr 02, 2018, 03:27 PM
1IF EXISTS(SELECT 'TRUE' FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = N'uspMoveInventory' AND ROUTINE_TYPE = N'Procedure')
2DROP PROC uspMoveInventory
3GO
4CREATE PROCEDURE uspMoveInventory
5 @ResourceType int,
6 @Quantity bigint,
7 @SourceCargoId uniqueidentifier, -- make an empty guid if we just need to Add inventory and not move it.
8 @DestinationCargoId uniqueidentifier,
9 @RequisitionId bigint = NULL -- req to complete if supplied
10AS
11 SET NOCOUNT ON;
12 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
13BEGIN
14 DECLARE @Ret int
15 DECLARE @CurQuantity bigint
16 DECLARE @InNestedTransaction BIT
17
18 BEGIN TRY
19
20 /* start transaction if one is not already there */
21 IF (@@TRANCOUNT = 0)
22 BEGIN
23 SET @InNestedTransaction = 0;
24 BEGIN TRAN
25 END
26 ELSE
27 SET @InNestedTransaction = 1;
28 /* end start transaction block */
29
30 /* Attempt to lock both source and destination Cargo. We can have other threads trying to deliver to, or take from them. Requires clustered index on CargoId and ResourceType for inventory table.
31 https://stackoverflow.com/questions/14264589/holdlock-with-updlock/14265153#14265153 */
32 SELECT COUNT(*) FROM Inventory with(UPDLOCK)
33 WHERE (CargoId=@SourceCargoId OR CargoId=@DestinationCargoId)-- and ResourceType=@ResourceType
34
35 /* if move operation, check to make sure we have enough actual inventory in the source to move */
36 IF
37 @SourceCargoId != CAST(0x0 AS UNIQUEIDENTIFIER) /* if moving, check source has enough quantity */
38 and
39 NOT EXISTS
40 (select @CurQuantity from Inventory
41 where CargoId=@SourceCargoId
42 and ResourceType=@ResourceType
43 and Quantity >= @Quantity)
44 BEGIN
45 set @Ret = 1
46 END
47 ELSE
48 BEGIN
49
50 /* add to destination */
51 UPDATE Inventory SET Quantity = Quantity + @Quantity
52 WHERE CargoId=@DestinationCargoId and ResourceType=@ResourceType
53
54 IF @@ROWCOUNT=0
55 begin
56 INSERT INTO Inventory
57 ([ResourceType], [Quantity], [CargoId])
58 VALUES (@ResourceType, @Quantity, @DestinationCargoId)
59 --IF @@error <> 0
60 --begin
61 -- RAISERROR ('Add to Destination, Insert Inventory failed',16,-1)
62 --end
63 end
64
65
66 /* subtract from source */
67 If (@SourceCargoId != CAST(0x0 AS UNIQUEIDENTIFIER)) /*'00000000-0000-0000-0000-000000000000'*/ /* if moving */
68 begin
69 update Inventory
70 SET Quantity = Quantity - @Quantity
71 WHERE CargoId=@SourceCargoId
72 and ResourceType=@ResourceType
73 end
74
75
76 /* see if there is a requisition to adjust as a result of this move. Don't complete it if we are partially filling it. */
77 /* what if another thread is changing the requisition quantity at the same time */
78 if (ISNULL(@RequisitionId, 0) != 0)
79 begin
80 /* adjust quantity */
81 UPDATE Requisitions
82 SET Quantity = Quantity - @Quantity
83 WHERE ID = @RequisitionId
84
85
86 /* complete the requsition if quantity has been filled. NOTE, it is possible to deliver more cargo than a requisition called for. */
87 UPDATE Requisitions
88 SET [Completed] = 1
89 WHERE ID = @RequisitionId
90 and Quantity <= 0
91 end
92
93 set @Ret = 0
94 END
95
96 /* start of commit. Only commit if we have a locally created transaction above. */
97 IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
98 COMMIT TRANSACTION
99
100 END TRY
101 BEGIN CATCH
102 set @Ret = 10
103
104 /* start rollback. Only roll back if we had a locally created transaction. */
105 IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
106 ROLLBACK
107
108 /* Throw exception so upper level code knows there is an error */
109 DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
110 @ErrorState INT = ERROR_STATE(),
111 @ErrorSeverity INT = ERROR_SEVERITY();
112 RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
113
114 END CATCH
115
116 return @Ret
117END
118GO