alexbancheva

Task11_Place Order_Exam27June2020

Feb 10th, 2021
622
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Task 11 Place Order
  2. CREATE PROCEDURE usp_PlaceOrder(@JobId int, @SerialNumber varchar(50), @Quantity int)
  3. AS
  4.     DECLARE @JobStatus VARCHAR(MAX) = (SELECT [Status]
  5.                                         FROM Jobs
  6.                                         WHERE JobId = @JobId)
  7.     DECLARE @JobExists BIT = (SELECT COUNT(JobId)
  8.                                 FROM Jobs
  9.                                 WHERE JobId = @JobId)
  10.  
  11.     DECLARE @PartExists BIT = (SELECT COUNT(SerialNumber)
  12.                                 FROM Parts
  13.                                 WHERE SerialNumber = @SerialNumber)
  14.  
  15.     IF (@Quantity <= 0)
  16.         THROW 50012, 'Part quantity must be more than zero!', 1;
  17.  
  18.     IF (@JobStatus = 'Finished')
  19.         THROW 50011, 'This job is not active!', 1;
  20.  
  21.     IF (@JobExists = 0)
  22.         THROW 50013, 'Job not found!', 1;
  23.  
  24.     IF (@PartExists = 0)
  25.         THROW 50014, 'Part not found!', 1;
  26.  
  27.     DECLARE @OrderForJobExists INT = (SELECT COUNT(o.OrderId)
  28.                                         FROM Orders AS o
  29.                                         WHERE o.JobId = @JobId AND o.IssueDate IS NULL)
  30.     IF (@OrderForJobExists = 0)
  31.         INSERT INTO Orders
  32.             VALUES
  33.                 (@JobId, NULL, 0);
  34.  
  35.     DECLARE @OrderId INT = (SELECT o.OrderId
  36.                             FROM Orders AS o
  37.                             WHERE o.JobId = @JobId AND o.IssueDate IS NULL);
  38.  
  39.     IF (@OrderId > 0 AND @PartExists = 0 AND @Quantity > 0)
  40.        
  41.         DECLARE @PartId INT = (SELECT PartId
  42.                                 FROM Parts
  43.                                 WHERE SerialNumber = @SerialNumber);
  44.         DECLARE @PartExistsInOrder INT = (SELECT COUNT(*)
  45.                                             FROM OrderParts
  46.                                             WHERE OrderId = @OrderId AND PartId = @PartId);
  47.         IF (@PartExistsInOrder > 0)
  48.             UPDATE OrderParts
  49.             SET Quantity += @Quantity
  50.             WHERE OrderId = @OrderId AND PartId = @PartId
  51.         ELSE
  52.         INSERT INTO OrderParts
  53.             VALUES
  54.                 (@OrderId, @PartId, @Quantity);
  55. GO
RAW Paste Data