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