SET NOCOUNT ON go -------------------------- Table Customers -------------------------------- -- A minimal customers table. If DeregDate is NULL, this means that the customer -- can not make new orders. CREATE TABLE Customers ( CustomerID int NOT NULL, CustomerName nvarchar(50) NOT NULL, DeregDate datetime NULL, CONSTRAINT pk_Customers PRIMARY KEY (CustomerID) ) INSERT Customers (CustomerID, CustomerName) VALUES (1, 'Napoleon Murphy Brock') INSERT Customers (CustomerID, CustomerName) VALUES (2, 'Jimmy Carl Black') INSERT Customers (CustomerID, CustomerName, DeregDate) VALUES (3, 'Ricky Lancelotti', '1980-04-07') INSERT Customers (CustomerID, CustomerName) VALUES (4, 'Captain Beefheart') INSERT Customers (CustomerID, CustomerName) VALUES (5, 'Ian Underwood') INSERT Customers (CustomerID, CustomerName) VALUES (6, 'Tommy Mars') INSERT Customers (CustomerID, CustomerName) VALUES (7, 'Steve Vai') INSERT Customers (CustomerID, CustomerName) VALUES (8, 'George Duke') INSERT Customers (CustomerID, CustomerName) VALUES (9, 'Peter Wolf') go ------------------------------- Table Products --------------------------------- -- A minimal Products table. MaxDiscount states how much lower in per cent the -- the order price is permitted to be vis-à-vis the catalogue price. CREATE TABLE Products ( ProductID int NOT NULL, ProductName nvarchar(50) NOT NULL, CatalogPrice decimal(10,2) NOT NULL, MaxDiscount int NULL, CONSTRAINT pk_Products PRIMARY KEY (ProductID) ) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(1, 'Over-Nite Sensation', 50, 10) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(2, 'We''re Only in It for the Money ', 35, 5) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(3, 'One Size Fits All', 60, NULL) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(4, 'Bongo Fury', 60, 10) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(5, 'Hot Rats', 100, 10) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(6, 'Jazz From Hell', 10, 50) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(7, 'Them or Us', 40, 10) INSERT Products (ProductID, ProductName, CatalogPrice, MaxDiscount) VALUES(8, 'Sheik Yer''bouti', 55, NULL) go ------------------------ Table Orders and OrderDetails -------------------------- -- A simple order table. OrderGuid identifies external orders and must be unique -- when non-NULL. State specifies where in the process the order is. Only an order -- which has State = 'N' may be modified. CREATE TABLE Orders ( OrderID int NOT NULL IDENTITY, OrderGuid uniqueidentifier NULL, OrderDate datetime NOT NULL, CustomerID int NOT NULL, CampaignCode varchar(6) NULL, State char(1) NOT NULL -- N(ew), P(rocessing), C(ompleted) CONSTRAINT ck_Orders_State CHECK (State IN ('N', 'P', 'C')) CONSTRAINT def_Orders_State DEFAULT 'N', CONSTRAINT pk_Orders PRIMARY KEY (OrderID), CONSTRAINT fk_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ) go -- This index does not work on SQL 2005. CREATE INDEX OrderGuid_ix ON Orders(OrderGuid) WHERE OrderGuid IS NOT NULL go -- A simple details table. Note cascading delete to the Orders table. (But not to -- Products!) CREATE TABLE OrderDetails ( OrderID int NOT NULL, ProductID int NOT NULL, Quantity int NOT NULL CONSTRAINT ck_OrderDetails_Quantity CHECK (Quantity > 0), OrderPrice decimal(10,2) NOT NULL, CONSTRAINT pk_OrderDetails PRIMARY KEY (OrderID, ProductID), CONSTRAINT fk_OrderDetails FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE, CONSTRAINT fk_OrderDetails_Products FOREIGN KEY (ProductID) REFERENCES Products (ProductID) ) go -- Sample orders. INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('77A320C1-FAF1-487D-B91E-7023C0264FB4', '20130511', 1, 'N') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 3, 3, 48 UNION ALL SELECT scope_identity(), 4, 1, 58 INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('301384C6-5E19-4AC8-847F-D1619E45709F', '20130501', 3, 'C') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) VALUES(scope_identity(), 1, 1, 50.50) INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('BCAF1E80-D5CA-4BAD-B648-6A2B82CFB9CB', '20130508', 8, 'N') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 1, 1, 49 UNION ALL SELECT scope_identity(), 3, 1, 60 UNION ALL SELECT scope_identity(), 4, 2, 55 INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('530EFABB-5AB4-4A37-90EA-BAB93F281762', '20130511', 4, 'N') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 4, 4, 60 INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('410D26C6-CA25-4D07-B048-E8516AD2AAAD', '20130511', 5, 'N') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 2, 1, 35 UNION ALL SELECT scope_identity(), 5, 1, 100 INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('9104F66E-EBAF-44DA-91D3-13E67DFC0655', '20130511', 6, 'P') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 8, 1, 55 INSERT Orders(OrderGuid, OrderDate, CustomerID, State) VALUES('D9723459-C7E6-4048-B4CD-4B97886BE7BD', '20130511', 6, 'N') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 8, 2, 60 INSERT Orders(OrderGuid, OrderDate, CustomerID, CampaignCode, State) VALUES('FB80D11F-0D24-457F-B4EF-F12897DEFE49', '20130512', 2, 'INDIAN', 'N') INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) SELECT scope_identity(), 2, 1, 33 go -------------------------- Tables OrderImportFiles and Details ------------------- -- These table tracks the files with external orders that we import. A file always -- transitions from NEW to WORK to DONE to SENT. CREATE TABLE OrderImportFiles (FileID int NOT NULL, FileName nvarchar(500) NOT NULL, FileGuid uniqueidentifier NOT NULL, Status char(4) NOT NULL CONSTRAINT ck_OrderImportFiles_Status CHECK (Status IN ('NEW', 'WORK', 'DONE', 'SENT')) CONSTRAINT def_OrderImportFiles_Status DEFAULT 'NEW', Received datetime NOT NULL CONSTRAINT def_OrderImportFiles_Received DEFAULT getdate(), Sent datetime NULL, CONSTRAINT pk_OrderImportFiles PRIMARY KEY (FileID), CONSTRAINT u_OrderImportFiles UNIQUE (FileGuid) ) go -- This index will not load on SQL 2005. CREATE UNIQUE INDEX one_work_only ON OrderImportFiles(Status) WHERE Status = 'WORK' go -- This table has one row for each external order in the file. The Data document -- is the XML for the order. Operation and GUID are extracted mainly to make the -- table easier to deal with for humans. InternalOrderID is the OrderID in Orders. -- Files with Status = N are processed, and processing leads to Status to be set -- to O or E. In the latter case, Message should have a value detailing the error. CREATE TABLE OrderImportFileItems (FileID int NOT NULL, RowNo int NOT NULL, Operation varchar(100) NULL, OrderGuid nvarchar(400) NULL, Data xml NOT NULL, InternalOrderID int NULL, Status char(1) NOT NULL CONSTRAINT ck_OrderImportFileItems_Status CHECK (Status IN ('N', 'O', 'E')) -- (N)ew, O(K), E(rror) CONSTRAINT def_OrderImportFileItems_Status DEFAULT 'N', Message nvarchar(2048) NULL, CONSTRAINT pk_OrderImportFileItems PRIMARY KEY (FileID, RowNo), CONSTRAINT fk_OrderImportFileItems FOREIGN KEY (FileID) REFERENCES OrderImportFiles (FileID) ON DELETE CASCADE, CONSTRAINT fk_OrderImportFileItems_Orders FOREIGN KEY (InternalOrderID) REFERENCES Orders(OrderID) ON DELETE SET NULL ) go --=============================================================================== --------------------------------- Validation stored procedures. -- Here follows three stored procedures that are used to validate something. They -- all take @procid as the first parameter, and they pass @procid to SqlEventLog, -- so that error will be logged for the caller, not the validation procedure. They -- don't have any TRY-CATCH but rely on that the caller has a CATCH block to trap -- errors. CREATE PROCEDURE ValidateCustomer @procid int, @CustomerID int AS DECLARE @CustomerName nvarchar(50), @DeregDate datetime SELECT @CustomerName = CustomerName, @DeregDate = DeregDate FROM Customers WHERE CustomerID = @CustomerID IF @CustomerName IS NULL BEGIN EXEC slog.sqleventlog_sp @procid, 'Illegal customer id = %1.', @p1 = @CustomerID END IF @DeregDate IS NOT NULL BEGIN EXEC slog.sqleventlog_sp @procid, 'Customer %1 (%2) was deregistered on %3.', @p1 = @CustomerID, @p2 = @CustomerName, @p3 = @DeregDate END go CREATE PROCEDURE ValidateProduct @procid int, @ProductID int, @OrderPrice int AS DECLARE @ProductName nvarchar(50), @CatalogPrice decimal(10,2), @MaxDiscount int SELECT @ProductName = ProductName, @CatalogPrice = CatalogPrice, @MaxDiscount = MaxDiscount FROM Products WHERE ProductID = @ProductID IF @CatalogPrice IS NULL BEGIN EXEC slog.sqleventlog_sp @procid, 'Illegal product id %1.', @p1 = @ProductID END IF @OrderPrice < @CatalogPrice * (100 - @MaxDiscount) / 100 BEGIN EXEC slog.sqleventlog_sp @procid, 'The order price is %1 for the product "%2", but the catalog price is %3 with a maximum discount of %4.', @p1 = @OrderPrice, @p2 = @ProductName, @p3 = @CatalogPrice, @p4 = @MaxDiscount END go CREATE PROCEDURE ValidateOrderGuid @procid int, @OrderGuid uniqueidentifier, @OrderID int = NULL OUTPUT AS DECLARE @State char(1) SELECT @State = State, @OrderID = OrderID FROM Orders WHERE OrderGuid = @OrderGuid IF @State IS NULL BEGIN EXEC slog.sqleventlog_sp @procid, 'No such order %1.', @p1 = @OrderGuid END ELSE IF @State <> 'N' BEGIN EXEC slog.sqleventlog_sp @procid, 'Order %1 is processed/shipped and cannot be modified.', @p1 = @OrderGuid END go ----------------------------- Procedure AddExternalOrder --------------------------- -- Adds header data for a new order. Since an order must have at least one product, -- it would be bad to call this procedure without an active transaction. CREATE PROCEDURE AddExternalOrder @OrderGuid uniqueidentifier, @CustomerID int, @CampaignCode varchar(6), @OrderID int OUTPUT AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY IF @@trancount = 0 OR @@nestlevel = 1 BEGIN EXEC slog.sqleventlog_sp @@procid, 'This procedure must be called from a transaction.' END IF EXISTS (SELECT * FROM Orders WHERE OrderGuid = @OrderGuid) BEGIN EXEC slog.sqleventlog_sp @@procid, 'Order %1 already exists.', @p1 = @OrderGuid END EXEC ValidateCustomer @@procid, @CustomerID INSERT Orders(OrderGuid, CustomerID, CampaignCode, OrderDate) VALUES(@OrderGuid, @CustomerID, nullif(@CampaignCode, ''), convert(char(8), getdate(), 112)) SELECT @OrderID = scope_identity() END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH go --------------------------- Procedure ModifyExternalOrder ---------------------- -- Modifies header data on an existing order. CREATE PROCEDURE ModifyExternalOrder @OrderGuid uniqueidentifier, @CustomerID int = NULL, @CampaignCode varchar(6) = NULL AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY EXEC ValidateOrderGuid @@procid, @OrderGuid IF @CustomerID IS NOT NULL EXEC ValidateCustomer @@procid, @CustomerID UPDATE Orders SET CustomerID = coalesce(@CustomerID, CustomerID), CampaignCode = CASE WHEN @CampaignCode IS NOT NULL THEN nullif(@CampaignCode, '') ELSE CampaignCode END WHERE OrderGuid = @OrderGuid END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH go ------------------------------- Procedure DeleteExternalOrder -------------- -- Deletes an external order. Relies on the cascading foreign key. CREATE PROCEDURE DeleteExternalOrder @OrderGuid uniqueidentifier AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY EXEC ValidateOrderGuid @@procid, @OrderGuid DELETE Orders WHERE OrderGuid = @OrderGuid END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH go -------------------------- Procedure ModifyExternalOrderDetail ------------------ -- Adds, modifies or deletes a product from an order. It is not permitted to -- delete the last remaining product on the order. CREATE PROCEDURE ModifyExternalOrderDetail @OrderGuid uniqueidentifier, @ProductID int, @Quantity int = NULL, @OrderPrice decimal(10,2) = NULL AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY DECLARE @OrderID int EXEC ValidateOrderGuid @@procid, @OrderGuid, @OrderID OUTPUT EXEC ValidateProduct @@procid, @ProductID, @OrderPrice IF NOT EXISTS (SELECT * FROM OrderDetails WHERE OrderID = @OrderID AND ProductID = @ProductID) BEGIN INSERT OrderDetails(OrderID, ProductID, Quantity, OrderPrice) VALUES(@OrderID, @ProductID, @Quantity, @OrderPrice) END ELSE IF @Quantity IS NULL OR @Quantity <> 0 BEGIN UPDATE OrderDetails SET Quantity = coalesce(@Quantity, Quantity), OrderPrice = coalesce(@OrderPrice, OrderPrice) WHERE OrderID = @OrderID AND ProductID = @ProductID END ELSE IF (SELECT COUNT(*) FROM OrderDetails WHERE OrderID = @OrderID) > 1 DELETE OrderDetails WHERE OrderID = @OrderID AND ProductID = @ProductID ELSE BEGIN EXEC slog.sqleventlog_sp @@procid, 'Attempt to delete the last product on order %1. Delete the entire order, or add another product first.', @p1 = @OrderGuid END END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH go --================================================================================ ------------------------------- Procedure AddOrderImportFile --------------------- -- Receives an XML document and store header information about in OrderImportFiles. -- Shreds the XML document into orders and saves the orders into -- OrderImportFileItems. Note that all extractions of data is made on the -- assumption that the file may be incorrect. Therefore all data is received -- into strings that are larger than needed for the expected data type. CREATE PROCEDURE AddOrderImportFile @Contents xml, @FileName nvarchar(500) AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY DECLARE @FileID int, @FileGuidStr nvarchar(400), @FileGuid uniqueidentifier, @nodecnt int SELECT @FileGuidStr = T.c.value('@Guid', 'nvarchar(400)'), @nodecnt = COUNT(*) OVER() FROM @Contents.nodes('/Orders') AS T(c) IF @nodecnt <> 1 BEGIN EXEC slog.sqleventlog_sp @@procid, 'Malformed or empty XML file, there should be a single Orders top node.' END BEGIN TRY SELECT @FileGuid = convert(uniqueidentifier, @FileGuidStr) END TRY BEGIN CATCH END CATCH IF @FileGuid IS NULL BEGIN EXEC slog.sqleventlog_sp @@procid, 'The file %1 contains an invalid file GUID "%2", or the GUID is missing altogether', @p1 = @FileName, @p2 = @FileGuid END IF EXISTS (SELECT * FROM OrderImportFiles WHERE FileGuid = @FileGuid) BEGIN EXEC slog.sqleventlog_sp @@procid, 'The file with GUID %1 has already been imported.', @p1 = @FileGuid END BEGIN TRANSACTION SELECT @FileID = coalesce(MAX(FileID), 0) + 1 FROM OrderImportFiles WITH (UPDLOCK) INSERT OrderImportFiles(FileID, FileName, FileGuid) VALUES(@FileID, @FileName, @FileGuid) INSERT OrderImportFileItems (FileID, RowNo, Operation, OrderGuid, Data) SELECT @FileID, row_number() OVER(ORDER BY T.c), upper(ltrim(T.c.value('@Operation', 'varchar(100)'))), T.c.value('@OrderID', 'nvarchar(400)'), T.c.query('.') FROM @Contents.nodes('/Orders/Order') AS T(c) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH go ---------------------------- Procedure ProcessOneImportFile ---------------------- -- Processes all orders in one file. This is the longest procedure in the pack. CREATE PROCEDURE ProcessOneImportFile @FileID int AS SET XACT_ABORT, NOCOUNT ON DECLARE @RowNo int, @Operation varchar(100), @data xml, @OrderGuidStr nvarchar(400), @OrderGuid uniqueidentifier, @OrderID int, @CustomerIDStr nvarchar(400), @CustomerID int, @CampaignCode nvarchar(100), @DetailRows xml, @ProductIDStr nvarchar(400), @ProductID int, @QuantityStr nvarchar(400), @Quantity int, @PriceStr nvarchar(400), @Price decimal(10, 2) DECLARE @Products TABLE (ProductID int NOT NULL) -- Because an error will lead to any transaction to be rolled back, without the -- error being reraised, we cannot permit to be called in a transaction. IF @@trancount > 0 BEGIN EXEC slog.sqleventlog_sp @@procid, 'This procedure must not be called in a transaction.' END -- From here the procedure is just a WHILE loop, with a TRY-CATCH block. WHILE 1 = 1 BEGIN TRY -- Initiate all loop variables. SELECT @RowNo = NULL, @Operation = NULL, @data = NULL, @OrderGuidStr = NULL, @OrderGuid = NULL, @OrderID = NULL, @CustomerIDStr = NULL, @CustomerID = NULL, @CampaignCode = NULL, @DetailRows = NULL, @ProductIDStr = NULL, @QuantityStr = NULL, @PriceStr = NULL, @ProductID = NULL, @Quantity = NULL, @Price = NULL DELETE @Products -- Get next order. SELECT TOP 1 @RowNo = RowNo, @Operation = Operation, @OrderGuidStr = OrderGuid, @data = Data FROM OrderImportFileItems WHERE FileID = @FileID AND Status = 'N' ORDER BY RowNo -- Quit if there are not any more. IF @RowNo IS NULL RETURN -- Attempt to convert the GUID to the correct data type. BEGIN TRY SELECT @OrderGuid = convert(uniqueidentifier, @OrderGuidStr) END TRY BEGIN CATCH END CATCH -- Something went wrong, or the order GUID was missing in the first place. IF @OrderGuid IS NULL BEGIN EXEC slog.sqleventlog_sp @@procid, 'Invalid or missing GUID "%1" for order.', @p1 = @OrderGuidStr END -- Extract header values. All values are extracted to strings. SELECT @CustomerIDStr = nullif(T.c.value('@CustomerID', 'nvarchar(400)'), ''), @CampaignCode = ltrim(T.c.value('@CampaignCode', 'nvarchar(100)')), @DetailRows = T.c.query('Products') FROM @data.nodes('Order') AS T(c) -- Attempt to get the customer ID, and raise an error if it fails. Note that -- a NULL CustomerID is permitted for some values on Operation. BEGIN TRY SELECT @CustomerID = convert(int, @CustomerIDStr) END TRY BEGIN CATCH EXEC slog.sqleventlog_sp @@procid, 'Invalid customer id = "%1".', @p1 = @CustomerIDStr END CATCH -- A token validation of the campaign code. IF len(@CampaignCode) > 6 BEGIN EXEC slog.sqleventlog_sp @@procid, 'Incorrectly formed campaign code.', @p1 = @CampaignCode END BEGIN TRANSACTION -- First handle the header information. For a new order, there is extra -- validation. IF @Operation = 'ADD' BEGIN IF @CustomerID IS NULL BEGIN EXEC slog.sqleventlog_sp @@procid, 'Customer not provided for new order.' END IF NOT EXISTS (SELECT * FROM @DetailRows.nodes('/Products/Product') AS T(c)) BEGIN EXEC slog.sqleventlog_sp @@procid, 'For a new order, there must be at least one product.' END EXEC AddExternalOrder @OrderGuid, @CustomerID, @CampaignCode, @OrderID OUTPUT END ELSE IF @Operation = 'MODIFY' EXEC ModifyExternalOrder @OrderGuid, @CustomerID, @CampaignCode ELSE IF @Operation = 'DELETE' EXEC DeleteExternalOrder @OrderGuid ELSE BEGIN EXEC slog.sqleventlog_sp @@procid, 'Invalid operation %1 for order %2.', @p1 = @Operation, @p2 = @OrderGuid END -- Now loop over the products. (Which is not a very good solution for this -- simple example, but imagine that ModifyExternalOrderDetail is a very -- complex stored procedure.) Again, all values are extracted to strings. DECLARE DetailsCur CURSOR STATIC LOCAL FOR SELECT ProductIDStr = nullif(T.c.value('@ProductID', 'nvarchar(400)'), ''), QuantityStr = nullif(T.c.value('@Quantity', 'nvarchar(400)'), ''), PriceStr = nullif(T.c.value('@Price', 'nvarchar(400)'), '') FROM @DetailRows.nodes('/Products/Product') AS T(c) ORDER BY QuantityStr DESC OPEN DetailsCur WHILE 1 = 1 BEGIN FETCH DetailsCur INTO @ProductIDStr, @QuantityStr, @PriceStr IF @@fetch_status <> 0 BREAK SELECT @ProductID = NULL, @Quantity = NULL, @Price = NULL IF @ProductIDStr IS NULL BEGIN EXEC slog.sqleventlog_sp @@procid, 'Product ID missing for detail entry.' END -- Get numeric values. For quantity we consider errors so unlikely that -- we don't have any special handling. SELECT @ProductID = convert(int, @ProductIDStr), @Quantity = convert(int, @QuantityStr), @Price = convert(decimal(10, 2), @PriceStr) IF EXISTS (SELECT * FROM @Products WHERE ProductID = @ProductID) BEGIN EXEC slog.sqleventlog_sp @@procid, 'Product %1 appears multiple times for the same order entry.', @p1 = @ProductID END INSERT @Products(ProductID) VALUES(@ProductID) IF @Operation = 'ADD' AND @Quantity = 0 EXEC slog.sqleventlog_sp @@procid, 'Quantity must be > 0 when adding a new order.' EXEC ModifyExternalOrderDetail @OrderGuid, @ProductID, @Quantity = @Quantity, @OrderPrice = @Price END DEALLOCATE DetailsCur -- Mark order as successfully processed and commit. UPDATE OrderImportFileItems SET InternalOrderID = CASE WHEN @Operation = 'ADD' THEN @OrderID END, Status = 'O', Message = NULL WHERE FileID = @FileID AND RowNo = @RowNo COMMIT TRANSACTION END TRY BEGIN CATCH -- Note that the CATCH handler is part of the WHILE loop, and we will move to -- the next order, with one exception. DECLARE @errmsg nvarchar(2048), @errno int -- Rollback the transaction. IF @@trancount > 0 ROLLBACK TRANSACTION -- Log the error in SqlEventLog. Note that @reraise is 0. EXEC slog.catchhandler_sp @@procid, @reraise = 0, @errno = @errno OUTPUT, @errmsg_aug = @errmsg OUTPUT -- Unless the error is a deadlock, mark the order as failed with an error and -- save the error message with it. IF @errno <> 1205 BEGIN IF @ProductIDStr IS NOT NULL BEGIN SELECT @errmsg = @errmsg + ' This occurred for product "' + @ProductIDStr + '".' END UPDATE OrderImportFileItems SET Status = 'E', Message = @errmsg WHERE FileID = @FileID AND RowNo = @RowNo END -- Get rid of the DetailsCur if it exists. BEGIN TRY DEALLOCATE DetailsCur END TRY BEGIN CATCH END CATCH END CATCH go ------------------------------------ Procedure ProcessImportFiles ---------------- -- Iterates over OrderImportFiles and calls ProcessOneImportFile for each file. -- The procedure uses an application lock to make sure that there are not any -- parallel executions of the procedure. CREATE PROCEDURE ProcessImportFiles AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY DECLARE @FileID int, @ret int, @LockTaken int EXEC @ret = sp_getapplock 'ProcessImportFiles', 'Exclusive', 'Session', 0 IF @ret < 0 BEGIN EXEC slog.sqleventlog_sp @@procid, 'At instance of this procedure is already running.' END SELECT @LockTaken = 1 WHILE 1 = 1 BEGIN SELECT @FileID = MIN(FileID) FROM OrderImportFiles WHERE Status IN ('WORK', 'NEW') IF @FileID IS NULL BREAK UPDATE OrderImportFiles SET Status = 'WORK' WHERE FileID = @FileID EXEC ProcessOneImportFile @FileID UPDATE OrderImportFiles SET Status = 'DONE' WHERE FileID = @FileID END EXEC sp_releaseapplock 'ProcessImportFiles', 'Session' END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION IF @LockTaken = 1 EXEC sp_releaseapplock 'ProcessImportFiles', 'Session' EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH go --============================================================================== ----------------------- TEST SECTION! ------------------------------------------ -- This is the section where we test the procedures. First a query with the -- initial orders. SELECT O.OrderID, O.OrderGuid, O.OrderDate, O.CampaignCode, C.CustomerName, P.ProductName, OD.Quantity, OD.OrderPrice FROM Orders O JOIN OrderDetails OD ON O.OrderID = OD.OrderID JOIN Customers C ON O.CustomerID = C.CustomerID JOIN Products P ON OD.ProductID = P.ProductID ORDER BY O.OrderID, P.ProductID go -- Here we create and load two XML documents. Most orders have errors, as we -- want to test the error handling. DECLARE @orderdoc xml SELECT @orderdoc = N' ' EXEC AddOrderImportFile @orderdoc, 'C:\Temp\Orderfile1.xml' SELECT @orderdoc = N' ' EXEC AddOrderImportFile @orderdoc, 'C:\Temp\Orderfile2.xml' go ----------------------------------- Process the test files! --------------------- DECLARE @logid int SELECT @logid = coalesce(MAX(logid), 0) FROM slog.sqleventlog EXEC ProcessImportFiles -- Dump the tables after having processed the files. SELECT * FROM OrderImportFiles SELECT * FROM OrderImportFileItems ORDER BY FileID, RowNo SELECT O.OrderID, O.OrderGuid, O.OrderDate, O.CampaignCode, C.CustomerName, P.ProductName, OD.Quantity, OD.OrderPrice FROM Orders O JOIN OrderDetails OD ON O.OrderID = OD.OrderID JOIN Customers C ON O.CustomerID = C.CustomerID JOIN Products P ON OD.ProductID = P.ProductID ORDER BY O.OrderID, P.ProductID SELECT * FROM slog.sqleventlog WHERE logid > @logid ORDER BY logid go --================================================================================= ------------------------------------------ Clean up ------------------------------- DROP PROCEDURE ValidateProduct, ValidateCustomer, ValidateOrderGuid, AddExternalOrder, ModifyExternalOrder, DeleteExternalOrder, ModifyExternalOrderDetail, AddOrderImportFile, ProcessImportFiles, ProcessOneImportFile DROP TABLE OrderImportFileItems, OrderImportFiles, OrderDetails, Orders, Products, Customers