Question

Create a stored procedure named prc_return_video enter data about the return of videos that had been rented. The procedure should satisfy the following requirements.
a. The video number will be provided as a parameter.
b. Verify the video number exists in the VIDEO table. If it does not exist, display a message that the video number provided was not found and do not write any data to the database.
c. If the video number does exist, then use a Count() function to ensure that the video has only one record in DETAILRENTAL for which it does not have a return date. If more than one row in DETAILRENTAL indicates that the video is rented but not returned, display an error message that the video has multiple outstanding rentals and do not write any data to the database.
d. If the video does not have any outstanding rentals, then update the video status to “IN” for the video in the VIDEO table, and display a message that the video had no outstanding rentals but it is now available for rental. If the video has only one outstanding rental, then update the return date to the current system date, and update the video status to “IN” for that video in the VIDEO. Then display a message stating that the video was successfully returned.



$1.99
Sales0
Views78
Comments0
  • CreatedDecember 03, 2014
  • Files Included
Post your question
5000