a guest Feb 17th, 2017 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  2. --Stored procedure to create loan
  3. CREATE PROCEDURE uspCreateLoan (@userID INT, @itemID INT, @employeeID INT)
  4. AS
  5. BEGIN
  6. DECLARE @currentLoans INT = dbo.fnGetUserLoans(@userID) --Declaring variable currentLoans as the current amount of active loans from the user
  7. IF (SELECT maxItems FROM LibraryUser WHERE userID = @userID) > @currentLoans --Check if user has not surpassed max items they may loan at once
  8. BEGIN
  9.     IF (SELECT categoryName FROM Category --Do not let the user loan reference literature
  10.                 JOIN Item ON Item.CategoryID = Category.categoryID
  11.                 WHERE Item.itemID = @itemID) NOT LIKE 'Reference Literature'
  12.         BEGIN
  13.             IF (SELECT avaliable FROM Item WHERE itemID = @itemID) = 1 --If the item the user wants is avaliable then create a loan for that item
  14.             BEGIN
  15.                 DECLARE @dateAdd INT --Declaring dateadd and dueDate variables
  16.                 DECLARE @dueDate DATE
  17.                 SET @dateAdd = (SELECT maxLoanAllowance FROM Category --Setting dateAdd to the max amount of time you can loan that item
  18.                     JOIN Item ON Item.CategoryID = Category.categoryID
  19.                     WHERE Item.itemID = @itemID)
  20.                 SET @dueDate = GETDATE()+DATEADD(week,@dateAdd,@itemID) --calculating the due date by adding the current date with dateAdd
  22.                 INSERT INTO Loan (userID,employeeID,dueDate,itemID,returned) --Creating a new row in Loan with arguments supplied by the caller
  23.                 VALUES (@userID,@employeeID,@dueDate,@itemID,0)
  24.                 PRINT 'Thank you!'
  25.                 UPDATE Item SET avaliable = 0 WHERE itemId = @itemID --Setting item as unavaliable so it may not be loaned.
  26.                 RETURN
  27.             END
  28.             PRINT 'Unsuccessful, Item Unavaliable.'
  29.             RETURN
  30.         END
  31.         PRINT 'You may not loan reference literature'
  32.         RETURN
  33.     END
  34.     PRINT 'You have reached your loan limit. Return past loans.'
  35. END
  36. GO
RAW Paste Data