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
in a Stored procedure
- 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
Comments