TRY/CATCH and error handling in SQL server 2005/2008

SQL Server 2005 provides a robust set of tools to handle errors or exceptions. In the previous version, it was not easy to handle exceptions.
We use TRY/CATCH in SQL Server the same as popular languages, We write separate logic and exception handling code.

Syntax:
BEGIN TRY
-- Your Code or query
END TRY
BEGIN CATCH
-- Handling code
END CATCH

We write our logic code within TRY block and exception code in a CATCH block, if any error occurs in logic code it jumps to CATCH 

block, resume the execution and transaction is rollback.

Functions which is used in CATCH

1- ERROR_NUMBER() Returns error no of the error message.
2- ERROR_SEVERITY() returns the error severity regardless of how many times it is run.
3- ERROR_STATE() Returns the state number of error messages that cause the catch block.
4- ERROR_PROCEDURE() Return the Procedure Name.
5- ERROR_LINE() Return error line number.
6- ERROR_MESSAGE() return error description.

Note: If we are calling all the above methods outside of the CATCH block it will return NULL.

Example


CREATE PROCEDURE dbo.usp_TRY_CATCH_TEST

AS
BEGIN
BEGIN TRY
   SELECT 1/0
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER() AS error_no
   ,ERROR_SEVERITY() AS error_severity
   ,ERROR_STATE() AS error_state
   ,ERROR_PROCEDURE() AS error_procedure
   ,ERROR_LINE() AS error_lineNo
   ,ERROR_MESSAGE() AS error_message
END CATCH
END
 

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...