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