USE HotelGO--階段1：查詢入住在指定客房類型的客房的顧客數IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_GetGuestNumByTypeName')  DROP PROC usp_GetGuestNumByTypeNameGOCREATE PROCEDURE usp_GetGuestNumByTypeName    @typeName varchar(50),    ----客房類型名稱    @result int OUTPUT        ---返回值，居住在指定客房類型客房的顧客數AS     SELECT @result = count(1)    FROM GuestRecord     WHERE RoomID IN        (SELECT roomID FROM Room WHERE RoomTypeID=            (SELECT TypeID             FROM RoomType WHERE TypeName = @typeName))    PRINT @resultGO--調用存儲過程SET NOCOUNT ONDECLARE @Count intDECLARE @RoomType varchar(20)SET @RoomType = '標準間'EXEC usp_GetGuestNumByTypeName @RoomType,@Count OUTPUT PRINT '入住酒店' + @RoomType + '的客人總人數是:' + CAST(@Count AS varchar(10))

IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_GetRoomInfo')  DROP PROC usp_GetRoomInfoGOCREATE procedure usp_GetRoomInfo    @roomID intAS    IF @roomID=-1        SELECT             a.RoomID,            a.BedNum,            a.RoomStateID,            a.Description,            a.GuestNum,            a.RoomTypeID,            b.TypeName,            b.TypePrice,            RTRIM(c.RoomStateName) AS RoomStateName        FROM [Room] a         INNER JOIN [RoomType] b ON a.RoomTypeID = b.TypeID        INNER JOIN [RoomState] c ON a.RoomStateID = c.RoomStateID    ELSE        SELECT             a.RoomID,            a.BedNum,            a.RoomStateID,            a.Description,            a.GuestNum,            a.RoomTypeID,            b.TypeName,            b.TypePrice,            RTRIM(c.RoomStateName) AS RoomStateName        FROM Room a         INNER JOIN [RoomType] b ON a.RoomTypeID = b.TypeID        INNER JOIN [RoomState] c ON a.RoomStateID = c.RoomStateID        WHERE roomID = @roomIDGO--調用存儲過程/*DECLARE @RoomID intSET @RoomID = 1008EXEC usp_GetRoomInfo @RoomID*/EXEC usp_GetRoomInfo -1

IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_deleteRoomTypeById')  DROP PROC usp_deleteRoomTypeByIdGOCREATE PROCEDURE usp_deleteRoomTypeById    @typeName varchar(20) ----客房類型AS    DECLARE @typeID int    SELECT @typeID=TypeID FROM RoomType WHERE TypeName = @typeName    IF NOT EXISTS (SELECT * FROM Room WHERE RoomTypeID = @typeID) --Room表里沒有相關信息時才刪除    BEGIN      DELETE FROM RoomType WHERE TypeID=@typeID      return @@ROWCOUNT    END    ELSE      return -1GO--調用存儲過程DECLARE @RoomTYPE varchar(20)DECLARE @Result intSET @RoomTYPE  = '三人間'EXEC @Result=usp_deleteRoomTypeById @RoomTYPEIF (@Result > 0)  PRINT '刪除酒店客房類型是'+ @RoomTYPE +'的記錄' + CAST(@Result AS varchar(10)) + '條'ELSE  PRINT '刪除酒店客房類型是'+ @RoomTYPE +'的記錄，失敗'