There is nothing passed in to the procedure to indicate whether you want to insert/update the movie or delete the movie.
You should have two different procedures - one to insert or update:
CREATE PROCEDURE AddMovie
(
@Title NVARCHAR(10),
@Director NVARCHAR(10)
)
As
BEGIN
SET NOCOUNT ON;
UPDATE
Movies
SET
NumberOfCopies = NumberOfCopies + 1
WHERE
Title = @Title
And
Director = @Director
;
If @@ROWCOUNT = 0
BEGIN
INSERT INTO Movies
(
Title,
Director,
NumberOfCopies
)
VALUES
(
@Title,
@Director,
1
);
END;
SELECT
NumberOfCopies
FROM
Movies
WHERE
Title = @Title
And
Director = @Director
;
END
and one to delete:
CREATE PROCEDURE RemoveMovie
(
@Title NVARCHAR(10),
@Director NVARCHAR(10)
)
As
BEGIN
SET NOCOUNT ON;
BEGIN TRY;
BEGIN TRANSACTION;
DELETE
FROM R
FROM Reservations As R
INNER JOIN Movies As M ON M.IDMovie = R.IDMovie
WHERE M.Title = @Title
And M.Director = @Director;
DELETE
FROM Movies
WHERE Title = @Title
And Director = @Director;
COMMIT;
END TRY
BEGIN CATCH;
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH;
END