Exception handling SQL Server Store procedure.

Exception handling was biggest problem in store procedure before SQL Server 2005. This
features has been provided in SQL Server 2005. Exception handling is same as
other languages like C#, C++ etc. TRY..CATCH block is used for exception
handing.



TRY..CATCH Syntax





BEGIN TRY
--T-SQL statements / blocks
END TRY
BEGIN CATCH
--T-SQL statements / blocks
END CATCH




Example1: Error handling



Create proc Testproc
as
begin
BEGIN TRY
 -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
PRINT 'DIVIDE BY ZERO'
END CATCH
End



Functions to be used in CATCH block are :




ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.


ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.

ERROR_STATE: returns the state number of the error.

ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.

ERROR_LINE: returns the line number where the error occurred.

ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block. 







-- Create procedure to get error information.
CREATE PROCEDURE GetErrorInfo
AS
begin
SELECT
,ERROR_SEVERITY()
AS ErrorSeverity
ERROR_NUMBER()
AS ErrorNumber
,ERROR_STATE()
AS ErrorState
,ERROR_MESSAGE()
AS ErrorMessage;
,ERROR_PROCEDURE()
AS ErrorProcedure
,ERROR_LINE()
AS ErrorLine
GO
End


Use this store procedure in another store procedure

 to handle error






Create proc DividebyZero
BEGIN TRY
 -- Generate divide-by-zero error.
SELECT
1/0;
END TRY
 -- Execute error retrieval routine.
BEGIN
CATCH
EXECUTE GetErrorInfo;
END CATCH




Points to remember:

        

      1. A TRY…CATCH construct catches all execution errors that have a
severity higher than 10 that do not close the database.

     2.  A TRY block must be immediately followed by an associated CATCH
block.

     3. . A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH
construct cannot span multiple blocks of Transact-SQL statements. 
        
4. When the code in the CATCH block finishes, control passes to the
statement immediately after the END CATCH statement. 
 5. TRY…CATCH constructs can be nested. Either a TRY block or a
CATCH block can contain nested TRY…CATCH constructs.
 6. Errors encountered in a CATCH block are treated like errors
generated anywhere else. If the CATCH block contains a nested TRY…CATCH
construct, any error in the nested TRY block will pass control to the nested
CATCH block. If there is no nested TRY…CATCH construct, the error is passed
back to the caller.
 7. If the stored procedure does not contain its own TRY…CATCH
construct, the error returns control to the CATCH block associated with the TRY
block that contains the EXECUTE statement.
 8. If the stored procedure contains a TRY…CATCH construct, the
error transfers control to the CATCH block in the stored procedure. When the
CATCH block code finishes, control is passed back to the statement immediately
after the EXECUTE statement that called the stored procedure.
        
9. GOTO statements cannot be used to enter a TRY or CATCH block.
GOTO statements can be used to jump to a label inside the same TRY or CATCH
block or to leave a TRY or CATCH block.
        
10. The TRY…CATCH construct cannot be used in a user-defined
function.
        
11. The CATCH block is executed only if there is an error occurs in
T-SQL statements within TRY block otherwise the CATCH block is ignored.
        
12. Each TRY block is associated with only one CATCH block and vice
versa
        
13. TRY and CATCH blocks can’t be separated with the GO statement.
We need to put both TRY and CATCH blocks within the same batch.
       
14. TRY..CATCH blocks can be used with transactions. We check the
number of open transactions by using @@TRANCOUNT function in Sql Server.
     
15 XACT_STATE function within the TRY..CATCH block can be used to
check whether a open transaction is committed or not. It will return -1 if
transaction is not committed else returns 1.



Limitation of TRY…CATCH:

·        Compiled errors are not caught.

·  Deferred name resolution errors created by statement level recompilations. (If process
is terminated by Kill commands or broken client connections TRY…CATCH will be
not effective)

·   Errors with a severity greater than 10 that do not terminate their database connection
are caught in the TRY/CATCH block.

·     For errors that are not trapped, SQL Server
2005 passes control back to the application immediately, without executing any
CATCH block code.