[TimSoSachMuonTrongThangCuaLoaiSach].sql

CREATE PROCEDURE [dbo].[TimSoSachMuonTrongThangCuaLoaiSach]

        @Thang int,

        @MaLoaiSach char(4),

        @SoSach int OUTPUT

AS

BEGIN

        SET NOCOUNT ON;

DECLARE 

        @cur_garaa CURSOR,

        @cur_tailieu CURSOR,

        @phieumuon char(12),

        @tailieu char(11)

SET @SoSach = 0

SET @cur_garaa = CURSOR FORWARD_ONLY

        FOR SELECT MaPM FROM dbo.PHIEUMUON WHERE DATEPART(month, NgayMuon) = @Thang

OPEN @cur_garaa

        FETCH NEXT FROM @cur_garaa INTO @phieumuon

        WHILE @@FETCH_STATUS = 0

        BEGIN

                SET @cur_tailieu = CURSOR FORWARD_ONLY 

                        FOR SELECT MaTaiLieu FROM dbo.CT_PMS WHERE MaPM = @phieumuon

                OPEN @cur_tailieu

                        FETCH NEXT FROM @cur_tailieu INTO @tailieu

                        WHILE @@FETCH_STATUS = 0

                        BEGIN

                                IF @tailieu IN (SELECT MaSach FROM dbo.CUONSACH WHERE MaTuaDe IN (SELECT MaTuaDe FROM dbo.TUADESACH WHERE MaLoaiSach = @MaLoaiSach))

                                        BEGIN

                                                SET @SoSach = @SoSach + 1

                                        END

                                FETCH NEXT FROM @cur_tailieu INTO @tailieu

                        END

                CLOSE @cur_tailieu

                FETCH NEXT FROM @cur_garaa INTO @phieumuon

        END

CLOSE @cur_garaa

DEALLOCATE @cur_tailieu

DEALLOCATE @cur_garaa

END