We were once asked in class what TRY/CATCH doesn’t handle besides object existence errors.
It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are 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
- 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:
CREATE PROC usp_Table2_Purge (@purgedate datetime, @batch int = 5000)
SET NOCOUNT ON;
@rc int = 1, @retries tinyint = 0, @maxretries tinyint = 10,
@errormessage nvarchar(4000), @errorseverity int, @errorstate int;
WHILE @rc <> 0 AND @retries <= @maxRetries
DELETE TOP (@batch) Table2
ON Table2.Table1Id = Table1.Table1Id
WHERE Table1.CreatedDate < @purgeDate;
SELECT @rc = @@ROWCOUNT;
IF XACT_STATE() = 1 COMMIT TRANSACTION;
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
IF ERROR_NUMBER() = 1205 AND @retries < @maxRetries -- 1205 is the deadlock error
SET @retries = @retries + 1;
WAITFOR DELAY '00:00:10'; --pause to hopefully avoid deadlock next batch
ELSE -- some other error or done retrying
@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);
Brent says: when you’re looking at TRY/CATCH, it’s also good to remember the traditional guidance about transactions: keep ’em short. Do your selects, variable settings, background info gathering, etc before the transaction starts.