Recently i have completed online training program on T-SQL from Edx.
I just share in my blog that will cover all basic T-SQL.
-- Display all columns for all customers
SELECT * FROM SalesLT.Customer;
-- Display customer name fields
SELECT Title, FirstName, MiddleName, LastName, Suffix
FROM SalesLT.Customer;
-- Display title and last name with phone number
SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone
FROM SalesLT.Customer;
-- Customer Companies
SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany
FROM SalesLT.Customer;
--Sales Order Revisions
SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
FROM SalesLT.SalesOrderHeader;
-- Get middle names if known
SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '')+ LastName AS CustomerName
FROM SalesLT.Customer;
-- Get primary contact details
UPDATE SalesLT.Customer
SET EmailAddress = NULL
WHERE CustomerID % 7 = 1;
SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
FROM SalesLT.Customer;
-- Get shipping status
UPDATE SalesLT.SalesOrderHeader
SET ShipDate = NULL
WHERE SalesOrderID > 71899;
SELECT SalesOrderID, OrderDate,
CASE
WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
ELSE 'Shipped'
END AS ShippingStatus
FROM SalesLT.SalesOrderHeader;
--Retrieve City List
SELECT DISTINCT City, StateProvince
FROM SalesLT.Address
--Retrieve Heaviest Products
SELECT TOP 10 PERCENT Name FROM SalesLT.Product ORDER BY Weight DESC;
--Retrieve the Heaviest 100 Products Not Including the Heaviest Ten
SELECT Name FROM SalesLT.Product ORDER BY Weight DESC
OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;
--Retrieve Product Details
SELECT Name, Color, Size
FROM SalesLT.Product
WHERE ProductModelID = 1;
--Retrieve Products by Color and Size
SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE Color IN ('Black','Red','White') and Size IN ('S','M');
--Retrieve Products by Product Number
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-%';
--Retrieve Specific Products by Product Number
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';
-- Customer Orders
SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID;
-- Customer Orders with Addresses
SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2,
a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID;
-- All customers and their orders
SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
ORDER BY oh.SalesOrderID DESC;
-- Customers with no address
SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.AddressID IS NULL;
-- Customers and products for which there are no orders
SELECT c.CustomerID, p.ProductID
FROM SalesLT.Customer AS c
FULL JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
FULL JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
FULL JOIN SalesLT.Product AS p
ON p.ProductID = od.ProductID
WHERE oh.SalesOrderID IS NULL
ORDER BY ProductID, CustomerID;
-- Billing addresses
SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office';
-- Shipping addresses
SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping';
-- All customer addresses
SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
UNION ALL
SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName, AddressType;
-- Customers with only a main office address
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
EXCEPT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;
-- Only customers with both a main office and a shipping address
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
INTERSECT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;
--Retrieve the nname and approximate weight of each product
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;
--Retrieve the month and year products were first sold
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight,
YEAR(SellStartDate) as SellStartYear,
DATENAME(m, SellStartDate) as SellStartMonth
FROM SalesLT.Product;
-- Extract type from product number
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight,
YEAR(SellStartDate) as SellStartYear,
DATENAME(m, SellStartDate) as SellStartMonth,
LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product;
-- Filter to include only products with numeric sizes
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight,
YEAR(SellStartDate) as SellStartYear,
DATENAME(m, SellStartDate) as SellStartMonth,
LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product
WHERE ISNUMERIC(Size)=1;
--Retrieve Companies Ranked by Revenue
SELECT CompanyName,
TotalDue AS Revenue,
RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
FROM SalesLT.SalesOrderHeader AS SOH
JOIN SalesLT.Customer AS C
ON SOH.CustomerID=C.CustomerID;
--Retrieve Total Sales by Product
SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
GROUP BY P.Name
ORDER BY TotalRevenue DESC;
-- Only products that cost over $1,000
SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
ORDER BY TotalRevenue DESC;
--Only groupings with sales totals over $20,000
SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
HAVING SUM(LineTotal) > 20000
ORDER BY TotalRevenue DESC;
--Retrieve Products whose list price is higher than the average unit price in the SalesOrderDetail table
SELECT ProductID, Name, ListPrice from SalesLT.Product
WHERE ListPrice >
(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
ORDER BY ProductID;
--Retrieve products that are priced $100 or more
-- but have sold for a unit price of less than $100
SELECT ProductID, Name, ListPrice FROM SalesLT.Product
WHERE ProductID IN
(SELECT ProductID from SalesLT.SalesOrderDetail
WHERE UnitPrice < 100.00)
AND ListPrice >= 100.00
ORDER BY ProductID;
--Retrieve cost, list price, and average selling price for each product
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
ORDER BY P.ProductID;
--Find products where the average selling price is less than cost
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
WHERE StandardCost >
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID)
ORDER BY P.ProductID;
--Retrieve sales order data with customer information from a function
SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
ORDER BY SOH.SalesOrderID;
--Retrieve addresses with customer information from a function
SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City
FROM SalesLT.Address AS A
JOIN SalesLT.CustomerAddress AS CA
ON A.AddressID = CA.AddressID
CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
ORDER BY CA.CustomerID;
-- Retrieve product model descriptions
SELECT P.ProductID, P.Name AS ProductName, PM.Name AS ProductModel, PM.Summary
FROM SalesLT.Product AS P
JOIN SalesLT.vProductModelCatalogDescription AS PM
ON P.ProductModelID = PM.ProductModelID
ORDER BY ProductID;
-- Create a table of distinct colors
DECLARE @colors AS TABLE (Color nvarchar(15));
INSERT INTO @Colors
SELECT DISTINCT Color FROM SalesLT.Product;
SELECT ProductID, Name, Color
FROM SalesLT.Product
WHERE Color IN (SELECT Color FROM @Colors);
-- Retrieve product parent categories from a function
SELECT C.ParentProductCategoryName AS ParentCategory,
C.ProductCategoryName AS Category,
P.ProductID, P.Name AS ProductName
FROM SalesLT.Product AS P
JOIN dbo.ufnGetAllCategories() AS C
ON P.ProductCategoryID = C.ProductCategoryID
ORDER BY ParentCategory, Category, ProductName;
-- Get sales revenue by company and contact (using derived table)
SELECT CompanyContact, SUM(SalesAmount) AS Revenue
FROM
(SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
JOIN SalesLT.Customer AS c
ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)
GROUP BY CompanyContact
ORDER BY CompanyContact;
-- Get sales revenue by company and contact (using CTE)
WITH CustomerSales(CompanyContact, SalesAmount)
AS
(SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
JOIN SalesLT.Customer AS c
ON SOH.CustomerID = c.CustomerID)
SELECT CompanyContact, SUM(SalesAmount) AS Revenue
FROM CustomerSales
GROUP BY CompanyContact
ORDER BY CompanyContact;
--Initial query
SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY a.CountryRegion, a.StateProvince
ORDER BY a.CountryRegion, a.StateProvince;
-- With totals
SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
ORDER BY a.CountryRegion, a.StateProvince;
-- Subtotal levels
SELECT a.CountryRegion, a.StateProvince,
IIF(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,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
ORDER BY a.CountryRegion, a.StateProvince;
-- Including cities
SELECT a.CountryRegion, a.StateProvince, a.City,
CHOOSE (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,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
ORDER BY a.CountryRegion, a.StateProvince, a.City;
--Customer sales by parent category
SELECT * FROM
(SELECT cat.ParentProductCategoryName, cust.CompanyName, sod.LineTotal
FROM SalesLT.SalesOrderDetail AS sod
JOIN SalesLT.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
JOIN SalesLT.Customer AS cust ON soh.CustomerID = cust.CustomerID
JOIN SalesLT.Product AS prod ON sod.ProductID = prod.ProductID
JOIN SalesLT.vGetAllCategories AS cat ON prod.ProductcategoryID = cat.ProductCategoryID) AS catsales
PIVOT (SUM(LineTotal) FOR ParentProductCategoryName IN ([Accessories], [Bikes], [Clothing], [Components])) AS pivotedsales
ORDER BY CompanyName;
-- Insert a product
INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
VALUES
('LED Lights', 'LT-L123', 2.56, 12.99, 37, GETDATE());
SELECT SCOPE_IDENTITY();
SELECT * FROM SalesLT.Product
WHERE ProductID = SCOPE_IDENTITY();
-- Insert a new category with two products
INSERT INTO SalesLT.ProductCategory (ParentProductCategoryID, Name)
VALUES
(4, 'Bells and Horns');
INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
VALUES
('Bicycle Bell', 'BB-RING', 2.47, 4.99, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE()),
('Bicycle Horn', 'BH-PARP', 1.29, 3.75, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE());
SELECT c.Name As Category, p.Name AS Product
FROM SalesLT.Product AS p
JOIN SalesLT.ProductCategory as c ON p.ProductCategoryID = c.ProductCategoryID
WHERE p.ProductCategoryID = IDENT_CURRENT('SalesLT.ProductCategory');
-- Update product prices
UPDATE SalesLT.Product
SET ListPrice = ListPrice * 1.1
WHERE ProductCategoryID =
(SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
-- Discontinue products
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductCategoryID = 37
AND ProductNumber <> 'LT-L123';
-- Delete a product category and its products
DELETE FROM SalesLT.Product
WHERE ProductCategoryID =
(SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
DELETE FROM SalesLT.ProductCategory
WHERE ProductCategoryID =
(SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
-- Insert sales order header
DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;
DECLARE @OrderID int;
SET @OrderID = NEXT VALUE FOR SalesLT.SalesOrderNumber;
INSERT INTO SalesLT.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID, ShipMethod)
VALUES
(@OrderID, @OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');
PRINT @OrderID;
-- Insert sales order details
DECLARE @SalesOrderID int
DECLARE @ProductID int = 760;
DECLARE @Quantity int = 1;
DECLARE @UnitPrice money = 782.99;
SET @SalesOrderID = 0; -- test with the order ID generated for the sales order header inserted above
IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)
BEGIN
INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
VALUES
(@SalesOrderID, @Quantity, @ProductID, @UnitPrice)
END
ELSE
BEGIN
PRINT 'The order does not exist'
END
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(ListPrice), @AWMax = MAX(ListPrice)
FROM SalesLT.Product
WHERE ProductCategoryID IN
(SELECT DISTINCT ProductCategoryID
FROM SalesLT.vGetAllCategories
WHERE ParentProductCategoryName = 'Bikes');
IF @AWMax >= @MarketMax
BREAK
ELSE
CONTINUE
END
PRINT 'New average bike price:' + CONVERT(varchar, @AWAverage);
PRINT 'New maximum bike price:' + CONVERT(varchar, @AWMax);
DECLARE @SalesOrderID int = 0
-- uncomment the following line to delete an existing record
-- SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader;
BEGIN TRY
IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderID)
BEGIN
-- Throw a custom error if the specified order doesn't exist
DECLARE @error varchar(25);
SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';
THROW 50001, @error, 0
END
ELSE
BEGIN
DELETE FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderID;
DELETE FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderID;
END
END TRY
BEGIN CATCH
-- Catch and print the error
PRINT ERROR_MESSAGE();
END CATCH
DECLARE @SalesOrderID int = 0
-- uncomment the following line to specify an existing order
-- SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader;
BEGIN TRY
IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderID)
BEGIN
-- Throw a custom error if the specified order doesn't exist
DECLARE @error varchar(25);
SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';
THROW 50001, @error, 0
END
ELSE
BEGIN
BEGIN TRANSACTION
DELETE FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderID;
-- THROW 50001, 'Unexpected error', 0 --Uncomment to test transaction
DELETE FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderID;
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
-- Rollback the transaction and re-throw the error
ROLLBACK TRANSACTION;
THROW;
END
ELSE
BEGIN
-- Report the error
PRINT ERROR_MESSAGE();
END
END CATCH
Comments
Post a Comment