Looking to be sure that errors get caught properly in my stored procedure

Question

I'm looking to see if I am able to capture my errors correctly in this stored procedure:

ALTER PROCEDURE [dbo].[sp_UpdateText] 
    (@aID AS INT, 
     @CompanyID AS INT,
     @CompanyName AS VARCHAR(MAX))
AS
BEGIN
    DECLARE @Result VARCHAR(MAX)

    BEGIN TRY
        SET @Result = (SELECT dbo.[udf_StripHTMLTags](@CompanyName))  -- UDF function that strips HTML tags off my text field

        BEGIN TRANSACTION
            UPDATE __TestTable1
            SET CompanyName = @Result
            WHERE aid = @aid AND CompanyID = @CompanyID

            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNumber INT = ERROR_NUMBER();
        DECLARE @ErrorLine INT = ERROR_LINE();

        PRINT 'ERROR NUMBER: ' + CAST(@ErrorNumber as Varchar(10));
        PRINT 'ERROR LINE: ' + CAST (@ErrorLine as Varchar(10));
    END CATCH
END
Go

I'm basically hoping that these BEGIN TRY BEGIN CATCH error capture methods will successfully capture errors, if arise? Any thought?

Answer 1

You should check out Erland's Guide to Error Handling

A suggestion from this inclusive guide would be to change your CATCH at a minimum to

   BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION   --roll back the tran
        DECLARE @msg nvarchar(2048) = error_message()  --error message is usually more helpful
        DECLARE @ErrorNumber INT = ERROR_NUMBER();
        DECLARE @ErrorLine INT = ERROR_LINE();
        RAISERROR(@msg,16,1) --RAISE the error 
        RETURN 55555 --return a non-zero to application as non-success
    END CATCH

There's a lot more in there which is why it's worth the read.

I almost forgot, SET XACT_ABORT, NOCOUNT ON at the top of your proc.

When you activate XACT_ABORT ON, almost all errors have the same effect: any open transaction is rolled back and execution is aborted. There are a few exceptions of which the most prominent is the RAISERROR statement.

Note that “printing” the error would not store or log it anywhere, like the SQL Server Error log so you wouldn’t “catch” it at all.