TJdjsvn0

1-- Display all columns for all customers
2SELECT * FROM SalesLT.Customer;
3
4-- Display customer name fields
5SELECT Title, FirstName, MiddleName, LastName, Suffix
6FROM SalesLT.Customer;
7
8-- Display title and last name with phone number
9SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone
10FROM SalesLT.Customer;
11
12-- Customer Companies
13SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany
14FROM SalesLT.Customer;
15
16--Sales Order Revisions
17SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
18	   CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
19FROM SalesLT.SalesOrderHeader;
20
21-- Get middle names if known
22SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '')+ LastName AS CustomerName
23FROM SalesLT.Customer;
24
25-- Get primary contact details
26UPDATE SalesLT.Customer
27SET EmailAddress = NULL
28WHERE CustomerID % 7 = 1;
29
30SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
31FROM SalesLT.Customer;
32
33-- Get shipping status
34UPDATE SalesLT.SalesOrderHeader
35SET ShipDate = NULL
36WHERE SalesOrderID > 71899;
37
38SELECT SalesOrderID, OrderDate,
39    CASE
40      WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
41      ELSE 'Shipped'
42    END AS ShippingStatus
43FROM SalesLT.SalesOrderHeader;
44
45--Retrieve City List
46SELECT DISTINCT City, StateProvince
47FROM SalesLT.Address
48
49--Retrieve Heaviest Products
50SELECT TOP 10 PERCENT Name FROM SalesLT.Product ORDER BY Weight DESC;
51
52--Retrieve the Heaviest 100 Products Not Including the Heaviest Ten
53SELECT Name FROM SalesLT.Product ORDER BY Weight DESC
54OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;
55
56--Retrieve Product Details
57SELECT Name, Color, Size
58FROM SalesLT.Product
59WHERE ProductModelID = 1;
60
61--Retrieve Products by Color and Size
62SELECT ProductNumber, Name
63FROM SalesLT.Product
64WHERE Color IN ('Black','Red','White') and Size IN ('S','M');
65
66--Retrieve Products by Product Number
67SELECT ProductNumber, Name, ListPrice
68FROM SalesLT.Product 
69WHERE ProductNumber LIKE 'BK-%';
70
71--Retrieve Specific Products by Product Number
72SELECT ProductNumber, Name, ListPrice
73FROM SalesLT.Product 
74WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';
75
76-- Customer Orders
77SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
78FROM SalesLT.Customer AS c
79JOIN SalesLT.SalesOrderHeader AS oh
80ON oh.CustomerID = c.CustomerID;
81
82-- Customer Orders with Addresses
83SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2,
84	   a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue
85FROM SalesLT.Customer AS c
86JOIN SalesLT.SalesOrderHeader AS oh
87ON oh.CustomerID = c.CustomerID
88JOIN SalesLT.CustomerAddress AS ca
89ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'
90JOIN SalesLT.Address AS a
91ON ca.AddressID = a.AddressID;
92
93-- All customers and their orders
94SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue
95FROM SalesLT.Customer AS c
96LEFT JOIN SalesLT.SalesOrderHeader AS oh
97ON c.CustomerID = oh.CustomerID
98ORDER BY oh.SalesOrderID DESC;
99
100-- Customers with no address
101SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone
102FROM SalesLT.Customer AS c
103LEFT JOIN SalesLT.CustomerAddress AS ca
104ON c.CustomerID = ca.CustomerID
105WHERE ca.AddressID IS NULL;
106
107-- Customers and products for which there are no orders
108SELECT c.CustomerID, p.ProductID
109FROM SalesLT.Customer AS c
110FULL JOIN SalesLT.SalesOrderHeader AS oh
111ON c.CustomerID = oh.CustomerID
112FULL JOIN SalesLT.SalesOrderDetail AS od
113ON od.SalesOrderID = oh.SalesOrderID
114FULL JOIN SalesLT.Product AS p
115ON p.ProductID = od.ProductID
116WHERE oh.SalesOrderID IS NULL
117ORDER BY ProductID, CustomerID;
118
119-- Billing addresses
120SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
121FROM SalesLT.Customer AS c
122JOIN SalesLT.CustomerAddress AS ca
123ON c.CustomerID = ca.CustomerID
124JOIN SalesLT.Address AS a
125ON ca.AddressID = a.AddressID
126WHERE ca.AddressType = 'Main Office';
127
128-- Shipping addresses
129SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
130FROM SalesLT.Customer AS c
131JOIN SalesLT.CustomerAddress AS ca
132ON c.CustomerID = ca.CustomerID
133JOIN SalesLT.Address AS a
134ON ca.AddressID = a.AddressID
135WHERE ca.AddressType = 'Shipping';
136
137-- All customer addresses
138SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
139FROM SalesLT.Customer AS c
140JOIN SalesLT.CustomerAddress AS ca
141ON c.CustomerID = ca.CustomerID
142JOIN SalesLT.Address AS a
143ON ca.AddressID = a.AddressID
144WHERE ca.AddressType = 'Main Office'
145UNION ALL
146SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
147FROM SalesLT.Customer AS c
148JOIN SalesLT.CustomerAddress AS ca
149ON c.CustomerID = ca.CustomerID
150JOIN SalesLT.Address AS a
151ON ca.AddressID = a.AddressID
152WHERE ca.AddressType = 'Shipping'
153ORDER BY c.CompanyName, AddressType;
154
155-- Customers with only a main office address
156SELECT c.CompanyName
157FROM SalesLT.Customer AS c
158JOIN SalesLT.CustomerAddress AS ca
159ON c.CustomerID = ca.CustomerID
160JOIN SalesLT.Address AS a
161ON ca.AddressID = a.AddressID
162WHERE ca.AddressType = 'Main Office'
163EXCEPT
164SELECT c.CompanyName
165FROM SalesLT.Customer AS c
166JOIN SalesLT.CustomerAddress AS ca
167ON c.CustomerID = ca.CustomerID
168JOIN SalesLT.Address AS a
169ON ca.AddressID = a.AddressID
170WHERE ca.AddressType = 'Shipping'
171ORDER BY c.CompanyName;
172
173-- Only customers with both a main office and a shipping address
174SELECT c.CompanyName
175FROM SalesLT.Customer AS c
176JOIN SalesLT.CustomerAddress AS ca
177ON c.CustomerID = ca.CustomerID
178JOIN SalesLT.Address AS a
179ON ca.AddressID = a.AddressID
180WHERE ca.AddressType = 'Main Office'
181INTERSECT
182SELECT c.CompanyName
183FROM SalesLT.Customer AS c
184JOIN SalesLT.CustomerAddress AS ca
185ON c.CustomerID = ca.CustomerID
186JOIN SalesLT.Address AS a
187ON ca.AddressID = a.AddressID
188WHERE ca.AddressType = 'Shipping'
189ORDER BY c.CompanyName;
190
191--Retrieve the nname and approximate weight of each product
192SELECT	ProductID,
193		UPPER(Name) AS ProductName,
194		ROUND(Weight, 0) AS ApproxWeight
195FROM SalesLT.Product;
196
197--Retrieve the month and year products were first sold
198SELECT	ProductID,
199		UPPER(Name) AS ProductName,
200		ROUND(Weight, 0) AS ApproxWeight,
201		YEAR(SellStartDate) as SellStartYear,
202		DATENAME(m, SellStartDate) as SellStartMonth
203FROM SalesLT.Product;
204
205-- Extract type from product number
206SELECT	ProductID,
207		UPPER(Name) AS ProductName,
208		ROUND(Weight, 0) AS ApproxWeight,
209		YEAR(SellStartDate) as SellStartYear,
210		DATENAME(m, SellStartDate) as SellStartMonth,
211		LEFT(ProductNumber, 2) AS ProductType
212FROM SalesLT.Product;
213
214-- Filter to include only products with numeric sizes
215SELECT	ProductID,
216		UPPER(Name) AS ProductName,
217		ROUND(Weight, 0) AS ApproxWeight,
218		YEAR(SellStartDate) as SellStartYear,
219		DATENAME(m, SellStartDate) as SellStartMonth,
220		LEFT(ProductNumber, 2) AS ProductType
221FROM SalesLT.Product
222WHERE ISNUMERIC(Size)=1;
223
224--Retrieve Companies Ranked by Revenue
225SELECT	CompanyName,
226		TotalDue AS Revenue,
227		RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
228FROM SalesLT.SalesOrderHeader AS SOH
229JOIN SalesLT.Customer AS C
230ON SOH.CustomerID=C.CustomerID;
231
232--Retrieve Total Sales by Product
233SELECT Name,SUM(LineTotal) AS TotalRevenue
234FROM SalesLT.SalesOrderDetail AS SOD
235JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
236GROUP BY P.Name
237ORDER BY TotalRevenue DESC;
238
239-- Only products that cost over $1,000
240SELECT Name,SUM(LineTotal) AS TotalRevenue
241FROM SalesLT.SalesOrderDetail AS SOD
242JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
243WHERE P.ListPrice > 1000
244GROUP BY P.Name
245ORDER BY TotalRevenue DESC;
246
247--Only groupings with sales totals over $20,000
248SELECT Name,SUM(LineTotal) AS TotalRevenue
249FROM SalesLT.SalesOrderDetail AS SOD
250JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
251WHERE P.ListPrice > 1000
252GROUP BY P.Name
253HAVING SUM(LineTotal) > 20000
254ORDER BY TotalRevenue DESC;
255
256--Retrieve Products whose list price is higher than the average unit price in the SalesOrderDetail table
257SELECT ProductID, Name, ListPrice from SalesLT.Product
258WHERE ListPrice >
259(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
260ORDER BY ProductID;
261
262--Retrieve products that are priced $100 or more
263-- but have sold for a unit price of less than $100
264SELECT ProductID, Name, ListPrice FROM SalesLT.Product
265WHERE ProductID IN
266(SELECT ProductID from SalesLT.SalesOrderDetail
267 WHERE UnitPrice < 100.00)
268AND ListPrice >= 100.00
269ORDER BY ProductID;
270
271--Retrieve cost, list price, and average selling price for each product
272SELECT ProductID, Name, StandardCost, ListPrice,
273	(SELECT AVG(UnitPrice)
274	 FROM SalesLT.SalesOrderDetail AS SOD
275	 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
276FROM SalesLT.Product AS P
277ORDER BY P.ProductID;
278
279--Find products where the average selling price is less than cost
280SELECT ProductID, Name, StandardCost, ListPrice,
281(SELECT AVG(UnitPrice)
282 FROM SalesLT.SalesOrderDetail AS SOD
283 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
284FROM SalesLT.Product AS P
285WHERE StandardCost >
286(SELECT AVG(UnitPrice)
287 FROM SalesLT.SalesOrderDetail AS SOD
288 WHERE P.ProductID = SOD.ProductID)
289ORDER BY P.ProductID;
290--Retrieve sales order data with customer information from a function
291SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
292FROM SalesLT.SalesOrderHeader AS SOH
293CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
294ORDER BY SOH.SalesOrderID;
295
296--Retrieve addresses with customer information from a function
297SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City
298FROM SalesLT.Address AS A
299JOIN SalesLT.CustomerAddress AS CA
300ON A.AddressID = CA.AddressID
301CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
302ORDER BY CA.CustomerID;
303
304-- Retrieve product model descriptions
305SELECT P.ProductID, P.Name AS ProductName, PM.Name AS ProductModel, PM.Summary
306FROM SalesLT.Product AS P
307JOIN SalesLT.vProductModelCatalogDescription AS PM
308ON P.ProductModelID = PM.ProductModelID
309ORDER BY ProductID;
310
311-- Create a table of distinct colors
312DECLARE @colors AS TABLE (Color nvarchar(15));
313
314INSERT INTO @Colors
315SELECT DISTINCT Color FROM SalesLT.Product;
316
317SELECT ProductID, Name, Color
318FROM SalesLT.Product
319WHERE Color IN (SELECT Color FROM @Colors);
320
321
322-- Retrieve product parent categories from a function
323SELECT C.ParentProductCategoryName AS ParentCategory,
324       C.ProductCategoryName AS Category,
325       P.ProductID, P.Name AS ProductName
326FROM SalesLT.Product AS P
327JOIN dbo.ufnGetAllCategories() AS C
328ON P.ProductCategoryID = C.ProductCategoryID
329ORDER BY ParentCategory, Category, ProductName;
330
331-- Get sales revenue by company and contact (using derived table)
332SELECT CompanyContact, SUM(SalesAmount) AS Revenue
333FROM
334	(SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
335	 FROM SalesLT.SalesOrderHeader AS SOH
336	 JOIN SalesLT.Customer AS c
337	 ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)
338GROUP BY CompanyContact
339ORDER BY CompanyContact;
340
341-- Get sales revenue by company and contact (using CTE)
342WITH CustomerSales(CompanyContact, SalesAmount)
343AS
344(SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
345 FROM SalesLT.SalesOrderHeader AS SOH
346 JOIN SalesLT.Customer AS c
347 ON SOH.CustomerID = c.CustomerID)
348SELECT CompanyContact, SUM(SalesAmount) AS Revenue
349FROM CustomerSales
350GROUP BY CompanyContact
351ORDER BY CompanyContact;
352
353--Initial query
354SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
355FROM SalesLT.Address AS a
356JOIN SalesLT.CustomerAddress AS ca
357ON a.AddressID = ca.AddressID
358JOIN SalesLT.Customer AS c
359ON ca.CustomerID = c.CustomerID
360JOIN SalesLT.SalesOrderHeader as soh
361ON c.CustomerID = soh.CustomerID
362GROUP BY a.CountryRegion, a.StateProvince
363ORDER BY a.CountryRegion, a.StateProvince;
364
365-- With totals
366SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
367FROM SalesLT.Address AS a
368JOIN SalesLT.CustomerAddress AS ca
369ON a.AddressID = ca.AddressID
370JOIN SalesLT.Customer AS c
371ON ca.CustomerID = c.CustomerID
372JOIN SalesLT.SalesOrderHeader as soh
373ON c.CustomerID = soh.CustomerID
374GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
375ORDER BY a.CountryRegion, a.StateProvince;
376
377-- Subtotal levels
378SELECT a.CountryRegion, a.StateProvince,
379IIF(GROUPING_ID(a.CountryRegion) = 1 AND GROUPING_ID(a.StateProvince) = 1, 'Total', IIF(GROUPING_ID(a.StateProvince) = 1, a.CountryRegion + ' Subtotal', a.StateProvince + ' Subtotal')) AS Level,
380SUM(soh.TotalDue) AS Revenue
381FROM SalesLT.Address AS a
382JOIN SalesLT.CustomerAddress AS ca
383ON a.AddressID = ca.AddressID
384JOIN SalesLT.Customer AS c
385ON ca.CustomerID = c.CustomerID
386JOIN SalesLT.SalesOrderHeader as soh
387ON c.CustomerID = soh.CustomerID
388GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
389ORDER BY a.CountryRegion, a.StateProvince;
390
391-- Including cities
392SELECT a.CountryRegion, a.StateProvince, a.City,
393CHOOSE (1 + GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City), a.City + ' Subtotal', a.StateProvince + ' Subtotal', a.CountryRegion + ' Subtotal', 'Total') AS Level,
394SUM(soh.TotalDue) AS Revenue
395FROM SalesLT.Address AS a
396JOIN SalesLT.CustomerAddress AS ca
397ON a.AddressID = ca.AddressID
398JOIN SalesLT.Customer AS c
399ON ca.CustomerID = c.CustomerID
400JOIN SalesLT.SalesOrderHeader as soh
401ON c.CustomerID = soh.CustomerID
402GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
403ORDER BY a.CountryRegion, a.StateProvince, a.City;
404
405--Customer sales by parent category
406SELECT * FROM
407(SELECT cat.ParentProductCategoryName, cust.CompanyName, sod.LineTotal
408 FROM SalesLT.SalesOrderDetail AS sod
409 JOIN SalesLT.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
410 JOIN SalesLT.Customer AS cust ON soh.CustomerID = cust.CustomerID
411 JOIN SalesLT.Product AS prod ON sod.ProductID = prod.ProductID
412 JOIN SalesLT.vGetAllCategories AS cat ON prod.ProductcategoryID = cat.ProductCategoryID) AS catsales
413PIVOT (SUM(LineTotal) FOR ParentProductCategoryName IN ([Accessories], [Bikes], [Clothing], [Components])) AS pivotedsales
414ORDER BY CompanyName;
415
416-- Insert a product
417INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
418VALUES
419('LED Lights', 'LT-L123', 2.56, 12.99, 37, GETDATE());
420
421SELECT SCOPE_IDENTITY();
422
423SELECT * FROM SalesLT.Product
424WHERE ProductID = SCOPE_IDENTITY();
425
426-- Insert a new category with two products
427INSERT INTO SalesLT.ProductCategory (ParentProductCategoryID, Name)
428VALUES
429(4, 'Bells and Horns');
430
431INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
432VALUES
433('Bicycle Bell', 'BB-RING', 2.47, 4.99, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE()),
434('Bicycle Horn', 'BH-PARP', 1.29, 3.75, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE());
435
436SELECT c.Name As Category, p.Name AS Product
437FROM SalesLT.Product AS p
438JOIN SalesLT.ProductCategory as c ON p.ProductCategoryID = c.ProductCategoryID
439WHERE p.ProductCategoryID = IDENT_CURRENT('SalesLT.ProductCategory');
440-- Update product prices
441UPDATE SalesLT.Product
442SET ListPrice = ListPrice * 1.1
443WHERE ProductCategoryID =
444	(SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
445
446-- Discontinue products
447UPDATE SalesLT.Product
448SET DiscontinuedDate = GETDATE()
449WHERE ProductCategoryID = 37
450AND ProductNumber <> 'LT-L123';
451
452-- Delete a product category and its products
453DELETE FROM SalesLT.Product
454WHERE ProductCategoryID =
455	(SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
456
457DELETE FROM SalesLT.ProductCategory
458WHERE ProductCategoryID =
459	(SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
460
461-- Insert sales order header
462DECLARE @OrderDate datetime = GETDATE();
463DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
464DECLARE @CustomerID int = 1;
465DECLARE @OrderID int;
466
467SET @OrderID = NEXT VALUE FOR SalesLT.SalesOrderNumber;
468
469INSERT INTO SalesLT.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID, ShipMethod)
470VALUES
471(@OrderID, @OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');
472
473PRINT @OrderID;
474
475-- Insert sales order details
476DECLARE @SalesOrderID int
477DECLARE @ProductID int = 760;
478DECLARE @Quantity int = 1;
479DECLARE @UnitPrice money = 782.99;
480
481SET @SalesOrderID = 0; -- test with the order ID generated for the sales order header inserted above
482
483IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)
484BEGIN
485	INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
486	VALUES
487	(@SalesOrderID, @Quantity, @ProductID, @UnitPrice)
488END
489ELSE
490BEGIN
491	PRINT 'The order does not exist'
492END
493
494DECLARE @MarketAverage money = 2000;
495DECLARE @MarketMax money = 5000;
496DECLARE @AWMax money;
497DECLARE @AWAverage money;
498
499SELECT @AWAverage =  AVG(ListPrice), @AWMax = MAX(ListPrice)
500FROM SalesLT.Product
501WHERE ProductCategoryID IN
502	(SELECT DISTINCT ProductCategoryID
503	 FROM SalesLT.vGetAllCategories
504	 WHERE ParentProductCategoryName = 'Bikes');
505
506WHILE @AWAverage < @MarketAverage
507BEGIN
508   UPDATE SalesLT.Product
509   SET ListPrice = ListPrice * 1.1
510   WHERE ProductCategoryID IN
511	(SELECT DISTINCT ProductCategoryID
512	 FROM SalesLT.vGetAllCategories
513	 WHERE ParentProductCategoryName = 'Bikes');
514	  
515	SELECT @AWAverage =  AVG(ListPrice), @AWMax = MAX(ListPrice)
516	FROM SalesLT.Product
517	WHERE ProductCategoryID IN
518	(SELECT DISTINCT ProductCategoryID
519	 FROM SalesLT.vGetAllCategories
520	 WHERE ParentProductCategoryName = 'Bikes');
521
522   IF @AWMax >= @MarketMax
523      BREAK
524   ELSE
525      CONTINUE
526END
527PRINT 'New average bike price:' + CONVERT(varchar, @AWAverage);
528PRINT 'New maximum bike price:' + CONVERT(varchar, @AWMax);
529
530
531DECLARE @SalesOrderID int = 0
532
533-- uncomment the following line to delete an existing record
534-- SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader; 
535
536BEGIN TRY
537	IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader
538				   WHERE SalesOrderID = @SalesOrderID)
539	BEGIN
540		-- Throw a custom error if the specified order doesn't exist
541		DECLARE @error varchar(25);
542		SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';
543		THROW 50001, @error, 0
544	END
545	ELSE
546	BEGIN
547		DELETE FROM SalesLT.SalesOrderDetail
548		WHERE SalesOrderID = @SalesOrderID;
549
550		DELETE FROM SalesLT.SalesOrderHeader
551		WHERE SalesOrderID = @SalesOrderID;
552	END
553END TRY
554BEGIN CATCH
555	-- Catch and print the error
556	PRINT  ERROR_MESSAGE();
557END CATCH
558
559
560DECLARE @SalesOrderID int = 0
561
562-- uncomment the following line to specify an existing order
563-- SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader; 
564
565BEGIN TRY
566	IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader
567				   WHERE SalesOrderID = @SalesOrderID)
568	BEGIN
569		-- Throw a custom error if the specified order doesn't exist
570		DECLARE @error varchar(25);
571		SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';
572		THROW 50001, @error, 0
573	END
574	ELSE
575	BEGIN
576	  BEGIN TRANSACTION
577		DELETE FROM SalesLT.SalesOrderDetail
578		WHERE SalesOrderID = @SalesOrderID;
579
580		-- THROW 50001, 'Unexpected error', 0 --Uncomment to test transaction
581
582		DELETE FROM SalesLT.SalesOrderHeader
583		WHERE SalesOrderID = @SalesOrderID;
584	  COMMIT TRANSACTION
585	END
586END TRY
587BEGIN CATCH
588	IF @@TRANCOUNT > 0
589	BEGIN
590		-- Rollback the transaction and re-throw the error
591		ROLLBACK TRANSACTION;
592		THROW;
593	END
594	ELSE
595	BEGIN
596		-- Report the error
597		PRINT  ERROR_MESSAGE();
598	END
599END CATCH