Use Of Try and Catch in sql server

Use of Try and Catch i n sql server

SQL Server 2005 has a number of new system error functions for returning error information. These new functions can be used in a CATCH block to help identify why an error occurred. Below is a list of the error functions available to CATCH blocks in SQL Server 2005, and the information each of these functions return:

ERROR_NUMBER(): Returns a number associated with the error.

ERROR_SEVERITY(): Returns the severity of the error.

ERROR_STATE(): Returns the error state number associated with the error.

ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger in which the error occurred.

ERROR_LINE(): Returns the line number inside the failing routine that caused the error.

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

These error functions can only be used within the scope of a CATCH block. If these functions are used outside a CATCH block, they will return a NULL value.

Examples:--by mithilesh

--just try to enter value in cart table which CartId column is primary key ,,,
--CartId 10 is already present in Cart table

begin try

insert into Cart(CartId) values(10)

end try

begin catch

select Error_message(),error_line(),error_number(),error_severity()

end catch

output:-

PRIMARY KEY constraint 'PK_Cart'.not insert duplicate key in object 'dbo.Cart'. 4 2627 14

/// using divide by zero error

BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

output:--

8134 16 1 NULL 3 Divide by zero error encountered.