query is not returning values

Hi mohammed shamsheer,
Try This

ALTER PROCEDURE [dbo].[InsertReservationDetailsforConf]                 

(               

 @RefNo bigint               

 ,@GuestID int               

 ,@RefferalPersonID int               

 ,@ReservationMode varchar(50)               

 ,@NoofPerson int               

 ,@EmployeeID int               

 ,@TransactionDate date               

 ,@CheckInDate date                

 ,@InTime datetime               

 ,@NoofHours int               

 ,@AdvanceAmount numeric(18,2)               

 ,@ConfirmationCode varchar(50)               

 ,@RoomID int               

 ,@Rent numeric(18,2)               

 ,@TaxPercentage numeric(18,2)               

 ,@VoucherType varchar(50)             

 ,@AppointmentId bigint       

 ,@BranchID bigint      

 ,@Total numeric(6,2)     

 ,@Discount numeric(6,2)     

 --,@ActionStatus varchar(5)     

)                 

AS                 

Begin                 

DECLARE @ReservationMasterID AS BIGINT                 

               

SELECT @ReservationMasterID=ISNULL(MAX(ReservationMasterID),0) FROM ReservationMaster                 

               

SET @ReservationMasterID=@ReservationMasterID+1                 

               

                 

DECLARE @ReservationDetailID AS BIGINT                 

               

SELECT @ReservationDetailID=ISNULL(MAX(ReservationDetailID),0) FROM ReservationDetailID                 

               

SET @ReservationDetailID=@ReservationDetailID+1                 

                 

DECLARE @ID AS BIGINT                 

SELECT @ID=ISNULL(MAX(InOutMasterID),0)FROM CheckInOut                 

SET @ID=@ID+1                 

                 

                 

INSERT INTO ReservationMaster                 

(               

 ReservationMasterID         

 ,BranchID             

 ,GuestID               

 ,RefferralPersonID               

 ,ReservationMode               

 ,NoofPerson               

 ,EmployeeID               

 ,TransactionDate               

 ,CheckInDate               

 ,InTime               

 ,NoofHours               

 ,AdvanceAmount               

 ,ConfirmationCode       

 ,IsActive      

 ,Ref_no      

 ,Total       

 ,Discount      

 ,ActionStatus      

)                 

                 

VALUES                 

                 

(               

 @ReservationMasterID        

 ,@BranchID              

,@GuestID         

,@RefferalPersonID               

,@ReservationMode               

,@NoofPerson               

,@EmployeeID               

,@TransactionDate               

,@CheckInDate               

,@InTime               

,@NoofHours               

,@AdvanceAmount               

,@ConfirmationCode      

,1     

 ,@RefNo      

 ,@Total     

 ,@Discount     

 ,'A'     

             

)  

 

 

         

              

                 

INSERT INTO ReservationDetailID                  

(               

  ReservationDetailID               

 ,ReservationMasterID               

 ,RoomID               

 ,Rent               

 ,Taxpercentage               

 ,IsActive               

)                 

                 

VALUES                 

(@ReservationDetailID,@ReservationMasterID,@RoomID,@Rent,@TaxPercentage,1)                 

                 

                 

INSERT INTO CheckInOut                 

(               

 InOutMasterID           

 ,ReservationMasterId             

 ,GuestID               

 ,RoomID               

 ,InTime               

 ,NoofPerson               

 ,CheckInTime               

 ,NoofHours               

 ,EmployeeID               

 ,CheckStatus               

 ,AdvanceAmount               

 ,IsActive               

)                 

                 

VALUES                 

(               

  @ID           

  ,@ReservationMasterID             

 ,@GuestID               

 ,@RoomID               

 ,@InTime               

 ,@NoofPerson               

 ,getdate()                 

 ,@NoofHours               

 ,@EmployeeID               

 ,'Reserved'               

 ,@AdvanceAmount               

 ,1               

)                 

                  

UPDATE CheckInOut SET GuestStatus='Not Yet Confirmed' WHERE RoomID=@RoomID AND IsActive=1                 

               

UPDATE Rooms SET  RoomStatus='Reserved' WHERE RoomID=@RoomID                 

               

UPDATE Vouchers SET   LastVoucherNumber=@RefNo  ,ModifiedDate=getdate()  WHERE VoucherType=@VoucherType              

               

UPDATE     Appointments SET IsActive=1     WHERE AppointmentID=@AppointmentId          

               

select  @ReservationMasterID  

 End