Monday, July 30, 2012

How to do error handling in Stored Procedures?

Create PROCEDURE [dbo].[usp_InsertContactInformation]
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 0
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY--Begin try
BEGIN TRANSACTION
/*
SET @ErrorTrackMessage='TrackPoint'
--We are going to use this @ErrorTrackMessage in catch block if something not caught in transaction Block.
IF @Error_No <> 0
BEGIN
SELECT CONVERT(VARCHAR,@Error_No)+',Error in Procesing the file' as Message
ROLLBACK TRANSACTION
--Perform Necessary Opeartion
RETURN
END --End of IF
ELSE
BEGIN
IF @@ERROR = 0
BEGIN
SET @ErrorTrackMessage = 'All Tranactions Completed'
END --End of IF
COMMIT TRANSACTION
SELECT 'Pass' as Message
--Perform Necessary Opeartion
RETURN
END--End of Inner Else
*/
END TRY--End Try
BEGIN CATCH--Begin Catch
/*
IF @ErrorTrackMessage <> 'TrackPoint'
BEGIN
SELECT CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),'')) + ',' + CONVERT(VARCHAR,ISNULL(ERROR_MESSAGE(),'')) + ' in ' + CONVERT(VARCHAR,ISNULL(ERROR_PROCEDURE(),'')) + ' at Line number ' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(),''))+ ' After ' + @ErrorTrackMessage
END
ELSE
BEGIN
SELECT CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),'')) + ',' + CONVERT(VARCHAR,ISNULL(ERROR_MESSAGE(),'')) + ' in ' + CONVERT(VARCHAR,ISNULL(ERROR_PROCEDURE(),'')) + ' at Line number ' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(),''))+ ' After ' + @ErrorTrackMessage +
'While Executing Logic'
END
ROLLBACK TRANSACTION
--Perform Necessary Opeartion
*/
END CATCH--End Catch
END