Monn_9999

Laboratorium3

Jan 3rd, 2021
1,090
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE TSQL2012
  2. GO
  3.  
  4. DROP PROCEDURE dbo.CustomerOrdersReport
  5. GO
  6.  
  7. CREATE PROCEDURE CustomerOrdersReport
  8.     @customer VARCHAR(128),
  9.     @datefrom DATE,
  10.     @dateto DATE
  11.  
  12. AS
  13.     BEGIN
  14.            SET @customer = (SELECT sc.companyname FROM Sales.Customers sc WHERE sc.companyname = @customer)
  15.            SET @datefrom = (SELECT * FROM Sales.Orders so WHERE so.orderdate = @datefrom)
  16.            SET @dateto = (SELECT * FROM Sales.Orders so WHERE so.orderdate = @dateto)
  17.     END
  18.  
  19. DROP PROCEDURE  dbo.deleteOrder
  20. GO
  21.  
  22. CREATE PROCEDURE deleteOrder
  23.     @orderid INT = NOT NULL,
  24.     @poscount INT,
  25.     @posvalue INT
  26. AS
  27.     BEGIN
  28.        SET NOCOUNT ON;
  29.        IF NOT EXISTS(SELECT orderid FROM SAles.OrderDetails WHERE orderid = @orderid)
  30.            BEGIN
  31.             RAISERROR('There is no order with this id or its equals 0', 16 , 1)
  32.            END;
  33.        ELSE IF((SELECT count(orderid) FROM Sales.OrderDetails WHERE orderid = @orderid) > @poscount)
  34.             BEGIN
  35.                 RAISERROR('Number of these orders are greater than provided number', 16, 1)
  36.             END;
  37.        ELSE IF((SELECT SUM(qty * unitprice) FROM Sales.OrderDetails WHERE orderid = @orderid) > @posvalue)
  38.             BEGIN
  39.                RAISERROR('The sum of orders is greater than provided', 16, 1)
  40.             END;
  41.        ELSE
  42.             BEGIN
  43.                 DELETE FROM Sales.OrderDetails WHERE orderid = @orderid
  44.                 PRINT 'Successfully Deleted'
  45.             END;
  46.     END
  47.  
  48. DROP PROCEDURE NewProduct
  49. GO
  50.  
  51. CREATE PROCEDURE NewProduct
  52.     @productname VARCHAR(28),
  53.     @categoryname VARCHAR(28),
  54.     @unitprice INT,
  55.     @suppliername VARCHAR(28)
  56. AS
  57.     BEGIN
  58.         SET NOCOUNT ON;
  59.         IF NOT EXISTS(SELECT * FROM Production.Products WHERE productname = @productname)
  60.             BEGIN
  61.                 INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice) VALUES(
  62.                 @productname, (SELECT supplierid FROM Production.Suppliers WHERE companyname = @suppliername),
  63.                 (SELECT categoryid FROM Production.Categories WHERE categoryname = @categoryname),
  64.                 @unitprice)
  65.             END
  66.         ELSE
  67.             BEGIN
  68.                 RAISERROR('This product is already exists', 1, 16)
  69.             END
  70.     END
RAW Paste Data