Skip to main content

Common T-SQL


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


















































Labels:

Comments

Post a Comment

Popular posts from this blog

C# run powershell script as administrator

Recently I was fetching a problem that I need to run a PowerShell script that will change TFS user Display name and SID. I was trying to run that script from C# that was not working due to TFS security update and TLS certificate. Using this code block I resolve the Issue. var newProcessInfo = new System.Diagnostics.ProcessStartInfo(); newProcessInfo.FileName = @"C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe"; newProcessInfo.Verb = "runas"; // Define Run as administrator newProcessInfo.Arguments = script; //Define your powershell script newProcessInfo.UseShellExecute = false; newProcessInfo.RedirectStandardOutput = true; // This will enable to read Powershell run output newProcessInfo.RedirectStandardError = true; Process proces = System.Diagnostics.Process.Start(newProcessInfo); proces.WaitForExit(); // I want to read the output string from powershell window StringBuilder output = new StringBuilder(); output.Append("Started"); while (!proces.St
Post a Comment
Read more

ASP.NET MVC razor SAP Crystal report

Crete a new project: Add a aspx Master Page Create a new folder Reports and 2 sub folder crystal & crystalviewer Now add a web form page in crystalviewer  folder. Add the master page namespace in your web form page. MasterPageFile ="~/Views/Shared/ReportSite.Master" Replace your web form by this code < asp : Content ID ="Content1" ContentPlaceHolderID ="ContentPlaceHolder1" runat ="server">      </ asp : Content > Now go to design mode of your web form drag & drop the crystal report viewer in your web form. After that your page will be look look like this. Replace the code: < CR : CrystalReportViewer ID ="EmployeeList" runat ="server"   HasCrystalLogo ="False"     AutoDataBind ="True"   Height ="50px"   EnableParameterPrompt ="false" EnableDatabaseLogonPrompt
Post a Comment
Read more

mvc razor textboxfor change event change another textboxfor value

Based on value of Weight, Rate , CNF & AWB it will change the value of Freight , TTLCNF anfd TTLFright . Freight= Weight*Rate; TTLCNF  = Weight*CNF; TTLFright=  Freight+ TTLCNF  + AWB; @Html.TextBoxFor(model => model.Weight, new { onChange="return GetWight(this);"}) @Html.TextBoxFor(model => model.Rate, new { onChange="return GetWight(this);"})/Kg @Html.TextBoxFor(model => model.Freight, new {disabled = "disabled" , @readonly = "readonly" ,onChange="return GetTTLFright(this);"}) @Html.TextBoxFor(model => model.CNFPK, new { onChange="return GetCNFPK(this);"}) @Html.TextBoxFor(model => model.TTLCNF, new {disabled = "disabled" , @readonly = "readonly",onChange="return GetTTLFright(this);" }) @Html.TextBoxFor(model => model.AWB, new { onChange="return GetTTLFright(this);"}) and script <script> function GetW
Post a Comment
Read more