Monday, 6 June 2016

SQL Server Stored Procedure Example, with Try-Catch, Function, TempTable,@@Error

Some time interviewer asked to write down a procedure with below functionality, This stored procedure Example will show you how use

  • Try catch block
  • Use of Transaction
  • Use of Temp table
  • Use of user defined Function
  • @@Error

in a Stored procedure


-- =============================================
-- Author: <Author,Vikas Ahlawat>
-- Create date: <Create 8th June 2012>
-- Description: <Description, It will return temp Room rates table according rooms>
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetTempRoomsRateTable]
(
@GuestID INT,@ArrayRoomList nvarchar(500), @DateFrom DATETIME,
@DateTo DATETIME,@Adult INT,@Child INT,@iHotelID INT,
@Discount DECIMAL,@TravalAgentID INT,@WantToSave BIT
)
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM fn_ReturnRateTable(@GuestID,@ArrayRoomList,@DateFrom,@DateTo,@Adult,@Child,@iHotelID,@Discount,@TravalAgentID)
BEGIN TRY
BEGIN TRAN
IF(@WantToSave = 1 AND @GuestID >0)
BEGIN
IF EXISTS (SELECT * FROM sys.tables WHERE name='#TempAppliedRate')
BEGIN
     DROP TABLE #TempAppliedRate
     END
     SELECT * INTO #TempAppliedRate FROM fn_ReturnRateTable(@GuestID,@ArrayRoomList,@DateFrom,@DateTo,@Adult,@Child,@iHotelID,@Discount,@TravalAgentID)
     DELETE FROM dbo.AppliedRateInfo WHERE iGuestID = @GuestID
     INSERT INTO dbo.AppliedRateInfo(iGuestID, RoomID,RoomNumber,PostingDate,RoomAmtWithoutDiscountAndTax,RoomAmtWithDiscount,Adults,Children,RoomType,DiscountPercent,Total,TravelAgentID ,TravelAgentCommissionPercent,TravelAgentCommission)
     SELECT @GuestID,RoomID,RoomNumber,PostingDate,RoomAmtWithoutDiscountAndTax,RoomAmtWithDiscount,Adults,Children,RoomType,DiscountPercent,Total,TravelAgentID ,TravelAgentCommissionPercent,TravelAgentCommission FROM #TempAppliedRate
END
COMMIT TRAN
END TRY
BEGIN CATCH
   IF(@@ERROR <> 0)
   ROLLBACK TRAN
   PRINT ERROR_MESSAGE()
END CATCH
SET NOCOUNT OFF

END
Visit Our New Site for HR Interview Questions Answers http://www.interviewboat.com/Company/GetCompany
Sponsored Ads

0 comments:

Post a Comment