USE [DBNAME]
GO
/****** Object: StoredProcedure [dbo].[f9001_pcrtw_Error] Script Date: 05/24/2011 12:13:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Author :Mahadevan
-- Create date :09/06/2010
-- Description :The procedure rethrows the original error
-- Modified Date :Modified By Reason
-- =====================================================================
CREATE PROCEDURE [dbo].[f9001_pcrtw_Error]
AS
BEGIN
SET NOCOUNT ON
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorLine INT
DECLARE @ErrorProcedure NVARCHAR(200)
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Building the message string that will contain original
-- error information.
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +'Message: '+ ERROR_MESSAGE();
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
)
SET NOCOUNT OFF
END
GO
Use the procedure "f9001_pcrtw_Error" along with other stored procedure as
CREATE PROCEDURE <name>
AS
BEGIN
BEGIN TRY
<SQL Statement>
END TRY
BEGIN CATCH
EXEC f9001_pctw_Error
END CATCH
END
No comments:
Post a Comment