--階段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