T-SQL: Programming and Error Handling

Lab 10

1.  Fill in the variable names to complete the DECLARE statements. You can infer these names from the INSERT statement further down the script. Finish the INSERT query. Because SalesOrderID is an IDENTITY column, this ID will automatically be generated for you. You can use the hardcoded value 'CARGO TRANSPORT 5' for the ShipMethod field. Use SCOPE_IDENTITY() to print out the ID of the new sales order header.

DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;

INSERT INTO SalesLT.SalesOrderHeader (OrderDate, DueDate, CustomerID, ShipMethod)
VALUES
(@OrderDate, @DueDate, @CustomerID, ‘CARGO TRANSPORT 5’);

PRINT SCOPE_IDENTITY();

2. Slightly adapted code from the previous exercise is available; it defines the OrderID with SCOPE_IDENTITY().

• The test should check to see if there is a SalesOrderDetail with a SalesOrderID that is equal to the OrderID exists in the SalesLT.SalesOrderHeader table.
• Finish the statement to insert a record in the SalesOrderDetail table when this is the case.
• Print out 'The order does not exist' when this is not the case.

— Code from previous exercise

DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;
INSERT INTO SalesLT.SalesOrderHeader (OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');
DECLARE @OrderID int = SCOPE_IDENTITY();

DECLARE @ProductID int = 760;
DECLARE @Quantity int = 1;
DECLARE @UnitPrice money = 782.99;

IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)
BEGIN
INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
VALUES (@OrderID, @Quantity, @ProductID, @UnitPrice)
END
ELSE
BEGIN
PRINT 'The order does not exist'
END

3. Adventure Works has determined that the market average price for a bike is $2,000, and consumer research has indicated that the maximum price any customer would be likely to pay for a bike is $5,000.

You must write some Transact-SQL logic that incrementally increases the list price for all bike products by 10% until the average list price for a bike is at least the same as the market average, or until the most expensive bike is priced above the acceptable maximum indicated by the consumer research.

The product categories in the Bikes parent category can be determined from the SalesLT.vGetAllCategoriesview.

• The loop should execute only if the average list price of a product in the ‘Bikes’ parent category is less than the market average.
• Update all products that are in the ‘Bikes’ parent category, increasing the list price by 10%.
• Determine the new average and maximum selling price for products that are in the ‘Bikes’ parent category.
• If the new maximum price is greater than or equal to the maximum acceptable price, exit the loop; otherwise continue.

DECLARE @MarketAverage money = 2000;DECLARE @MarketAverage money = 2000;
DECLARE @MarketMax money = 5000;
DECLARE @AWMax money;
DECLARE @AWAverage money;

SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)
FROM SalesLT.Product
WHERE ProductCategoryID IN
(SELECT DISTINCT ProductCategoryID
FROM SalesLT.vGetAllCategories
WHERE ParentProductCategoryName = 'Bikes');

WHILE @AWAverage < @MarketAverage
BEGIN
UPDATE SalesLT.Product
SET ListPrice = ListPrice * 1.1
WHERE ProductCategoryID IN
(SELECT DISTINCT ProductCategoryID
FROM SalesLT.vGetAllCategories
WHERE ParentProductCategoryName = 'Bikes');

SELECT @AWAverage = AVG(___), @AWMax = MAX(___)
FROM SalesLT.Product
WHERE ProductCategoryID IN (SELECT DISTINCT ProductCategoryID
FROM SalesLT.vGetAllCategories
WHERE ParentProductCategoryName = 'Bikes');

IF @AWMax >= @MarketMax
___
ELSE
CONTINUE
END
PRINT 'New average bike price:' + CONVERT(VARCHAR, @AWAverage);
PRINT 'New maximum bike price:' + CONVERT(VARCHAR, @AWMax);

Lab 11

Logging Errors

The following code can be used to delete order data:

DECLARE @OrderID int = ;
DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;

1. Modify the code to check for the existence of the specified order ID before attempting to delete it. If the order does not exist, your code should throw an error. Otherwise, it should go ahead and delete the order data.

DECLARE @OrderID int = 0DECLARE @OrderID int = 0
-- Declare a custom error if the specified order doesn't exist
DECLARE @error varchar(25) = 'Order #' + cast(@OrderID as varchar) + ' does not exist';
IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
BEGIN
-- Throw the custom error
THROW 50001, @error, 0;
END
ELSE
BEGIN
DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;
END

2. Add a TRY…CATCH to the code

• Include the IF–ELSE block in the TRY part.
• In the CATCH part, print the error with ERROR_MESSAGE();

DECLARE @OrderID int = 71774
DECLARE @error varchar(25) = 'Order #' + cast(@OrderID as varchar) + ' does not exist';

— Wrap IF ELSE in a TRY block
BEGIN TRY
IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
BEGIN
THROW 50001, @error, 0
END
ELSE
BEGIN
DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;
END
END TRY
-- Add a CATCH block to print out the error
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH

3. Add BEGIN TRANSACTION and COMMIT TRANSACTION to treat the two DELETE statements as a single transactional unit of work. In the error handler, modify the code so that if a transaction is in process, it is rolled back. If no transaction is in process the error handler should continue to simply print the error message.

DECLARE @OrderID int = 0DECLARE @OrderID int = 0

DECLARE @error varchar(25) = 'Order #' + cast(@OrderID as varchar) + ' does not exist';
BEGIN TRY

IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)

BEGIN
THROW 50001, @error, 0
END
ELSE
BEGIN

— Add code to treat as single transactional unit of work

BEGIN TRANSACTION
DELETE FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = @OrderID;
COMMIT TRANSACTION
END
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN -- Rollback the transaction
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
-- Report the error
PRINT ERROR_MESSAGE();
END
END CATCH