EXCEPTION HANDLING IN SQL SERVER 2005 USING TRY AND CATCH

1.SQL Server 2005 provides the TRY…CATCH construct.
2. TRY/CATCH helps the developers to write logic in the try block and error handling code in the catch block.
3. In code in TRY block fails, the control automatically jumps to the CATCH block.Code after failed statement will not get executed.
4. In catch block we can handle our errors or exception.

Basic Example of Divide by zero is given below

DECLARE @InputValue AS INT , @Output AS INT , @Interm AS INT
SET @InputValue = 20
BEGIN TRY
SET @Output = @InputValue / 0;
PRINT ‘1’ — This will not execute
END TRY
BEGIN CATCH
SET @Interm = @@ERROR
IF @Interm <> 0
BEGIN
PRINT ‘Output’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(@Interm AS VARCHAR(20))
PRINT ‘ERROR_NUMBER’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_NUMBER() AS VARCHAR(20))
PRINT ‘ERROR_SEVERITY’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_SEVERITY() AS VARCHAR(20))
PRINT ‘ERROR_STATE’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_STATE() AS VARCHAR(20))
PRINT ‘ERROR_LINE’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_LINE() AS VARCHAR(20))
PRINT ‘ERROR_MESSAGE’ + SPACE(1) + ‘:’ + CAST(ERROR_MESSAGE() AS VARCHAR(20))
END
ELSE
BEGIN
PRINT ‘Output’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(@Output AS VARCHAR(20))
END
END CATCH

Output
__________________________

Output : 8134
ERROR_NUMBER : 8134
ERROR_SEVERITY : 16
ERROR_STATE : 1
ERROR_LINE : 5
ERROR_MESSAGE :Divide by zero error

______________________________

Example 2  — Catch code will not get executed if we dont get any errors

DECLARE @InputValue AS INT , @Output AS INT , @Interm AS INT
SET @InputValue = 20
BEGIN TRY
SET @Output = @InputValue / 2;
PRINT ‘Output’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(@Output AS VARCHAR(20))
END TRY
BEGIN CATCH
SET @Interm = @@ERROR
IF @Interm <> 0
BEGIN
PRINT ‘Output’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(@Interm AS VARCHAR(20))
PRINT ‘ERROR_NUMBER’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_NUMBER() AS VARCHAR(20))
PRINT ‘ERROR_SEVERITY’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_SEVERITY() AS VARCHAR(20))
PRINT ‘ERROR_STATE’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_STATE() AS VARCHAR(20))
PRINT ‘ERROR_LINE’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(ERROR_LINE() AS VARCHAR(20))
PRINT ‘ERROR_MESSAGE’ + SPACE(1) + ‘:’ + CAST(ERROR_MESSAGE() AS VARCHAR(20))
END
ELSE
BEGIN
PRINT ‘Output’ + SPACE(1) + ‘:’ + SPACE(1) + CAST(@Output AS VARCHAR(20))
END
END CATCH

Output
________________

Output : 10

____________________________________________

Notes
1.@@ERROR and ERROR_NUMBER() are same.It returns the error number.
2.ERROR_SEVERITY() returns the severity of the error that caused the CATCH block of a TRY…CATCH construct to be run.
3.ERROR_STATE() returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
4.ERROR_LINE() returns the line number at which an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.
5.ERROR_MESSAGE() returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.

________________________________

We all are here to learn.

Pawan Kumar
Pawankkmr@hotmail.com