Part 8

usp_15NoDuplicateOrders

-- We need to ensure that by adding this order to the three tables we do not
-- produce and duplicates in the tables. What constitutes a duplicate?
-- We are relying on the primary keys to raise their own errors, 
-- but can we do some checking of our own?
DECLARE @RowCount INT;
DECLARE @ErrMsg VARCHAR(200)
SET @RowCount = 0;
SELECT OrderNumber FROM dbo.OrdersHistory 
	WHERE OrderNumber IN (SELECT OrderNumber FROM [dbo].[01HeaderBulkInsert]);
SET @RowCount = @@ROWCOUNT;
IF @RowCount > 0 
	BEGIN
		SET @ErrMsg = 'Error. Duplicate. This order has already been processed.';
		RAISERROR(@ErrMsg, 16,1);
	END