oef 4

CREATE PROCEDURE usp_dagVertoningenPerGenre
    @genre nvarchar(50)=NULL
AS

    DECLARE film_cursor CURSOR FOR
        SELECT m.movieName, g.genreDescription, ms.showTime, ms.cinemaID; ms.screenID FROM Movie m
        JOIN Genre g ON g.genreID = m.genreID
        JOIN MovieShow ms ON ms.movieID = m.movieID
        WHERE g.genreDescription = @genre AND
            (ms.showDatesStart < CURDATE() AND ms.showDatesEnd > CURDATE())

    OPEN film_cursor
    DECLARE @movieName nvarchar(50)
    DECLARE @genreDescription nvarchar(50)
    DECLARE @showTime TIME(7)
    DECLARE @cinemaID INT
    DECLARE @screenID INT

    FETCH NEXT FROM film_cursor
    INTO @movieName, @genreDescription, @showTime

    IF @@FETCH_STATUS != 0
        BEGIN
            PRINT 'Geen vertoningen vandaag!'
        END
    ELSE
        BEGIN
            PRINT '>> Overzicht films en vertoningen genre : ' + @genre

            WHILE @@FETCH_STATUS == 0
            BEGIN

                PRINT '==== Film: ' + (@movieName)
                PRINT '---->Cinema:' + (@cinemaID) + ' Zaal: ' + (@screenID) + ' Uur: ' + (@showTime)

                FETCH NEXT FROM film_cursor
                INTO @movieName, @genreDescription, @showTime
            END
        END