alexbancheva

Task_12_Switch Room_21June2020_Exam

Feb 6th, 2021
827
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE PROCEDURE usp_SwitchRoom(@TripId int, @TargetRoomId int)
  2. AS
  3.         DECLARE @SourceHotelId INT = (SELECT h.Id
  4.                                         FROM Hotels AS h
  5.                                         JOIN Rooms AS r ON r.HotelId = h.Id
  6.                                         JOIN Trips AS t ON t.RoomId = r.Id
  7.                                         WHERE t.Id = @TripId)
  8.  
  9.         DECLARE @TargetHotelId INT = (SELECT h.Id
  10.                                         FROM Hotels AS h
  11.                                         JOIN Rooms AS r ON h.Id = r.HotelId
  12.                                         WHERE r.Id = @targetRoomId)
  13.  
  14.         IF (@SourceHotelId <> @TargetHotelId)
  15.             THROW 50001, 'Target room is in another hotel!', 1
  16.  
  17.         DECLARE @PeopeCount INT = (SELECT COUNT(*)
  18.                                     FROM AccountsTrips
  19.                                     WHERE TripId = @TripId)
  20.         DECLARE @TargetRoomBeds INT = (SELECT Beds
  21.                                         FROM Rooms
  22.                                         WHERE Id = @TargetRoomId)
  23.         IF (@PeopeCount > @TargetRoomBeds)
  24.             THROW 50002, 'Not enough beds in target room!', 1
  25.  
  26.         UPDATE Trips
  27.         SET RoomId = @TargetRoomId
  28.         WHERE Id = @TripId
  29. GO
RAW Paste Data