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?
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.