Monn_9999

Laboratorium3

Jan 3rd, 2021
1,182
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×