Example : Stored Procedure with Try Catch

CREATE PROCEDURE [dbo].[e2w_spExample]

    @lang char(5),

    @chkUpdatCatalog bit,

    @Result varchar(500) OUT

AS

BEGIN TRY

    BEGIN TRANSACTION TR



           DECLARE @TableName varchar(100)

           DECLARE @QueryText nvarchar(max)

           Declare @IsCatalogTable bit

           Declare @ShouldAddMissingLangData bit

          

        DECLARE curTable CURSOR LOCAL FOR

        SELECT TableName, QueryText, IsCatalogRelatedTable FROM dbo.ssLanguageTables

        OPEN curTable

        FETCH NEXT FROM  curTable INTO @TableName, @QueryText, @IsCatalogTable

        WHILE (@@FETCH_STATUS = 0)

        BEGIN

       

        Set @ShouldAddMissingLangData = 1;

       

        If (@chkUpdatCatalog = 0)

        Begin

            if (@IsCatalogTable = 1)

            Begin

                Set @ShouldAddMissingLangData = 0;

            End

        End

       

        If (@ShouldAddMissingLangData = 1)

        Begin

            Set @QueryText = Replace(@QueryText, '@Lang', '''' + @Lang + '''') ;



            Print @QueryText;



            Execute (@QueryText);

           

            Print Cast(@@ROWCOUNT as varchar) + ' Rows affected for ' + @TableName + ' Table.'

        End

       

        FETCH NEXT FROM  curTable INTO @TableName, @QueryText, @IsCatalogTable

        END

        CLOSE curTable

        DEALLOCATE curTable

    IF(@@ERROR = 0 and @@TRANCOUNT>0)

        BEGIN

            COMMIT TRANSACTION TR

            Set @Result = '1'

        END

    ELSE

    BEGIN

        ROLLBACK TRANSACTION TR

            Set @Result = '0'

            SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState

                ,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;

    END



END TRY

BEGIN CATCH

        IF @@TRANCOUNT>0

            ROLLBACK TRANSACTION TR



        SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState

         ,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;



         Select @Result = ERROR_MESSAGE()

END CATCH