Is is possible to handle a UserAbort or ClientTimeOut within a Stored Procedure?

Question

An example:

create proc [dbo].[usp_SlowProc]         
as
begin try    
  set xact_abort on;  -- Ensures rollback on some errors that bypass the try/catch mechanism  
  raiserror('Waiting...', 0, 1) with nowait  
  waitfor delay '00:00:10'
  raiserror('Completed successfully.', 0, 1) with nowait  
end try
begin catch
    raiserror('Caught It.', 0, 1) with nowait   
    -- Rollback, log the error.
end catch

Then run it within SSMS/Toad:

exec dbo.usp_SlowProc;    

and then cancel it (using SSMS/Toad) before it completes. The catch block does not run.

Assuming from this link that a user abort is the same as as what .NET does on a client timeout (the result is the same).

SQL Server Transaction Timeout

Answer 1

AFAIK there is no way in TSQL to deal with this other than the way you currently are with XACT_ABORT. There are no structures that TSQL knows (or even cares about) as everything this deals with lives outside of TSQL.

What is actually happening is called an "Attention" event which could range from a connection closing to an actual cancel (for example the sqlcommand.cancel() method). Since this lives outside of the actual query, it just tells SQL Server that something happened and that it needs to clean up some stuff. The actual query has no idea what is going on.

Try/Catch is for query execution errors such as diving by 0, but not for any other type of errors. For example, compile/parse errors are not caught as the try/catch had never run. Since attention signals are not part of query execution (again, it's outside of that scope) it doesn't know anything about it and thus is not called as no execution exception has happened (according to the query itself).