What TRY/CATCH Doesn’t Handle In SQL Server

 It’s well documented in Books Online (BOL). If you’re like me, then we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some time too. At least these days it really is online.

Here’s a shortened version:

• Warnings or informational messages that have a severity of 10 or lower
• Errors that have a severity of 20 or higher that stop the session
• Attentions
• When a session is KILLed

In addition to the above, there are some errors that are not handled when it occurs at the same level as the TRY/CATCH:

• Compilation errors
• Errors that occur during statement-level recompilation (object existence errors fall into this category)

In all of these cases, be sure that the calling application/object handles the error.

My favorite usage of a TRY/CATCH construct is when you are purging data and retrying the DELETE due to a deadlock:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

CREATE PROC usp_Table2_Purge (@purgedate datetime, @batch int = 5000) AS SET NOCOUNT ON;   DECLARE   @rc int = 1, @retries tinyint = 0, @maxretries tinyint = 10,   @errormessage nvarchar(4000), @errorseverity int, @errorstate int;      WHILE @rc <> 0 AND @retries <= @maxRetries BEGIN;   BEGIN TRY;     BEGIN TRAN;       DELETE TOP (@batch) Table2     FROM Table2     JOIN Table1     ON Table2.Table1Id = Table1.Table1Id     WHERE Table1.CreatedDate < @purgeDate;          SELECT @rc = @@ROWCOUNT;            IF XACT_STATE() = 1 COMMIT TRANSACTION;   END TRY   BEGIN CATCH;     IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;        IF ERROR_NUMBER() = 1205 AND @retries < @maxRetries -- 1205 is the deadlock error     BEGIN;       SET @retries = @retries + 1;         WAITFOR DELAY '00:00:10'; --pause to hopefully avoid deadlock next batch     END;     ELSE -- some other error or done retrying     BEGIN;       SELECT           @errormessage = ERROR_MESSAGE()           + '. Error in line '           + CAST(ERROR_LINE() AS varchar(1000))           + ' of procedure ' + ERROR_PROCEDURE(),         @errorseverity = ERROR_SEVERITY(),         @errorstate = ERROR_STATE();                  RAISERROR (@errormessage, @errorseverity, @errorstate);         RETURN;     END;   END CATCH; END;   RETURN;

Brent says: when you’re looking at TRY/CATCH, it’s also good to remember the traditional guidance about transactions: keep them short. Do your selects, variable settings, background info gathering, etc before the transaction starts.

Article Courtesy : Brent Ozar