SQL Server Stored procedure jobs (ii)

Phase 1: Exercise--statistics on the number of guests staying in a certain room typeRequirements Descriptionuse stored procedures to count the total number of guests staying in a given room typeTips:The input parameter of the stored procedure is the specified room type name

 UseHotelGO--Phase 1: Check the number of guests staying in a room of a specified room typeIF EXISTS(SELECT *  fromsysobjectsWHEREName='Usp_getguestnumbytypename')  DROP PROCUsp_getguestnumbytypenameGOCREATE PROCEDUREUsp_getguestnumbytypename@typeName varchar( -),----Room type name    @result intOUTPUT---return value, number of guests residing in the specified room type as     SELECT @result = Count(1)     fromGuestrecordWHERERoomidinch        (SELECTRoomid fromTheWHERERoomtypeid=            (SELECTTypeID fromRoomtypeWHERETypeName= @typeName))    PRINT @resultGO--call a stored procedureSETNOCOUNT onDECLARE @Count intDECLARE @RoomType varchar( -)SET @RoomType = 'Standard Room'EXECUsp_getguestnumbytypename@RoomType,@CountOUTPUTPRINT 'stay at Hotel' + @RoomType + 'The total number of guests is:' + CAST(@Count  as varchar(Ten))

Stage 2: Practice--check room information according to room numberRequirements DescriptionCheck room number for information about the roomsIf the room number is-1 indicates all room informationTips:In the stored procedure, use the IF statement to determine if the input parameter is-1

IF EXISTS(SELECT *  fromsysobjectsWHEREName='Usp_getroominfo')  DROP PROCUsp_getroominfoGOCREATE procedureUsp_getroominfo@roomID int as    IF @roomID=-1        SELECTA.roomid, A.bednum, A.roomstateid, A.description, A.guestnum , A.roomtypeid, B.typename, B.typeprice,RTRIM(C.roomstatename) asRoomstatename from [ the]aINNER JOIN [Roomtype]B onA.roomtypeid=B.typeidINNER JOIN [roomstate]C onA.roomstateid=C.roomstateidELSE        SELECTA.roomid, A.bednum, A.roomstateid, A.description, A.guestnum, A.roomtypeid, B.typename, B.typeprice,RTRIM(C.roomstatename) asRoomstatename fromClass aINNER JOIN [Roomtype]B onA.roomtypeid=B.typeidINNER JOIN [roomstate]C onA.roomstateid=C.roomstateidWHERERoomid= @roomIDGO--call a stored procedure/*DECLARE @RoomID Intset @RoomID = 1008EXEC usp_getroominfo @RoomID*/EXECUsp_getroominfo-1

Phase 3: Exercise--Delete a room type residence recordRequirements DescriptionDelete room type records according to room typereturns the number of deleted records if the operation is successful; otherwise 1Tips:the input parameter is the specified room type nameUse the NOT EXISTS keyword to determine if the room information table exists the type of room to deletetake advantage of global variable @ @ROWCOUNT to get the number of records affected Return Receipt line result with return statement

IF EXISTS(SELECT *  fromsysobjectsWHEREName='Usp_deleteroomtypebyid')  DROP PROCUsp_deleteroomtypebyidGOCREATE PROCEDUREUsp_deleteroomtypebyid@typeName varchar( -)----Room type as    DECLARE @typeID int    SELECT @typeID=TypeID fromRoomtypeWHERETypeName= @typeName    IF  not EXISTS(SELECT *  fromTheWHERERoomtypeid= @typeID)--Delete when no relevant information is in the table    BEGIN      DELETE  fromRoomtypeWHERETypeID=@typeID      return @ @ROWCOUNT    END    ELSE      return -1GO--call a stored procedureDECLARE @RoomTYPE varchar( -)DECLARE @Result intSET @RoomTYPE  = 'Triple Room'EXEC @Result=Usp_deleteroomtypebyid@RoomTYPEIF(@Result > 0)  PRINT 'Delete hotel room type Yes'+ @RoomTYPE +'the records' + CAST(@Result  as varchar(Ten))+ 'Strip'ELSE  PRINT 'Delete hotel room type Yes'+ @RoomTYPE +'the record, failure'

SQL Server Stored procedure jobs (ii)