SQL Server存储过程作业（三）

--阶段4：添加一个入住客人的信息IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_insertGuestRecord')  DROP PROC usp_insertGuestRecordGOCREATE procedure usp_insertGuestRecord    @GuestID int OUTPUT,                ---客户流水号    @identityID varchar(50),            ---身份证号    @guestName nchar(20),                ---客户姓名    @roomID int,                        ---房间号    @ResideDate datetime,                ---入住时间    @deposit decimal(18,2) = 1000        ---押金AS    SET @GuestID = -1    IF (@identityID IS NULL OR LEN(@identityID) <> 18)        return -1    BEGIN TRANSACTION    INSERT INTO GuestRecord    (        IdentityID,        GuestName,        RoomID,        ResideID,        ResideDate,        Deposit)    VALUES    (        @identityID,        @guestName,        @roomID,        1,        @ResideDate,        @deposit    )    IF (@@ERROR <> 0)    BEGIN        ROLLBACK TRANSACTION        return 'false'    END    DECLARE @RoomStateID int    SELECT @RoomStateID=RoomStateID FROM RoomState    WHERE RoomStateName = '已入住'    ---客房状态变为“入住”，客人数量增1    Update Room set RoomStateID =@RoomStateID,GuestNum=GuestNum+1     WHERE RoomID = @roomID    IF (@@ERROR <> 0)    BEGIN        ROLLBACK TRANSACTION        return -1    END    COMMIT TRANSACTION    SET @GuestID=@@IDENTITY    return 0GO--调用存储过程DECLARE @identityID varchar(50)            ---身份证号DECLARE @guestName nchar(20)            ---客户姓名DECLARE @roomID int                        ---房间号DECLARE @deposit decimal(18,2)            ---押金DECLARE @ResideDate datetime            ---入住时间DECLARE @Result varchar(20)DECLARE @GuestID intSET @identityID = '11010119950506112x'SET @guestName = '风无痕'SET @roomID = 1008SET @deposit = 1000SET @ResideDate = GETDATE()EXEC @Result = usp_insertGuestRecord @GuestID OUTPUT,@identityID,@guestName,                                     @roomID,@ResideDate,@deposit IF (@Result = 0)BEGIN  PRINT '插入客人记录操作成功'   PRINT '客人编号是' + CAST(@GuestID AS varchar)ENDELSE  PRINT '插入客人记录操作失败' 

--查询在维修状态的房间信息create view  RoomStateNameas    select Room.RoomID,RoomType.TypeName,    RoomState.RoomStateName     from Room    inner  join RoomType on RoomType.TypeID=Room.RoomTypeID    inner join RoomState on RoomState.RoomStateID=Room.RoomStateID    where RoomState.RoomStateName='维修'goselect * from RoomStateName

--使用事物删除room表里面已经退房的旅客begin transaction declare @errorNum  int --错误的号码set @errorNum=0select *  into historyGuest from  GuestRecordwhere LeaveDate is not nullset @errorNum=@errorNum+@@ERRORdelete  from  GuestRecordwhere LeaveDate is not nullset @errorNum=@errorNum+@@ERRORif(@errorNum<>0)beginprint '删除失败，事物回滚'rollback transactionendelsebeginprint '删除成功'commit transactionend