SQL Server存储过程作业（二）

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 +'的记录，失败'