Advertisements

Archive

Posts Tagged ‘Exception Handling’

XACT_ABORT with TRANSACTIONS in SQL Server

December 1, 2011 2 comments

SQL Server by default does not ROLLBACK a Transaction if there is error anywhere in the code inside a BEGIN TRANSACTION & COMMIT TRANSACTION block. It only skips the statement that causes the error and moves ahead.

To allow a transaction to complete entirely or fail entirely you can use the TRY-CATCH feature with ROLLBACK statement or the SET XACT_ABORT statement.

By default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

–> Here let’s check how we can use XACT_ABORT with an example:


-- Create a test tabel and insert some records:
CREATE TABLE Tab (x INT NOT NULL PRIMARY KEY)

INSERT INTO Tab VALUES (1)
INSERT INTO Tab VALUES (2)
INSERT INTO Tab VALUES (3)
GO

-- =================================
-- Check with XACT_ABORT OFF option:
-- =================================
SET XACT_ABORT OFF; -- By default it is OFF

BEGIN TRANSACTION
	INSERT INTO Tab VALUES (4)
	INSERT INTO Tab VALUES (4) -- Statement fails but transaction is committed.
	INSERT INTO Tab VALUES (5)
COMMIT TRANSACTION
GO
/*
-- On message pane:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__Tab__3BD019E56991A7CB'. Cannot insert duplicate key in object 'dbo.Tab'. The duplicate key value is (4).
The statement has been terminated.

(1 row(s) affected)
*/

SELECT * FROM Tab
-- List records: 1,2,3,4,5
GO

-- ================================
-- Check with XACT_ABORT ON option:
-- ================================
SET XACT_ABORT ON

BEGIN TRANSACTION
	INSERT INTO Tab VALUES (6) -- Inserts 6, but the transaction is rollbacked in next step.
	INSERT INTO Tab VALUES (6) -- Statement fails and rollbacks the entire transaction.
	INSERT INTO Tab VALUES (7) -- Statement skipped as transaction is rollbacked at previous statement.
COMMIT TRANSACTION

SET XACT_ABORT OFF
GO
/*
-- On message pane:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__Tab__3BD019E56991A7CB'. Cannot insert duplicate key in object 'dbo.Tab'. The duplicate key value is (6).
*/

SELECT * FROM Tab
-- Still list records: 1,2,3,4,5
GO

-- Final cleanup
DROP TABLE Tab
GO

We can also use TRY-CATCH constructs that helps in handling transaction and can be used instead of XACT_ABORT statement. Check here how we can use them.

Advertisements

TRY CATCH – Exception Handling in SQL Server

June 16, 2010 11 comments

Similar to C++, Java and other languages SQL Server also has a mechanism to handle exceptions by using TRY-CATCH construct. The TRY block contains the SQL statements that may raise an error and CATCH block contains the handling mechanism to process the error. When any error is raised in the TRY block the control is immediately transferred to the CATCH block, where the Error is handled.

–> Following rules should be taken care off while using TRY-CATCH constructs:
– A TRY block must be followed immediately by the CATCH block.
– Both TRY & CATCH blocks must be inside a Batch, Stored Procedure or a Trigger.
– Only Errors with severity between 10 & 20 that do not close the database connection are caught & handled by TRY-CATCH constructs.

– As per MS BOL, Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. And Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.

–> let’s check how to use TRY-CATCH block:

USE [tempdb]
GO

--// Create a test Stored Procedure
CREATE PROC testPrc (@val VARCHAR(10))
AS
BEGIN
 SELECT 1/@val AS operation
END
GO

--// Test for Divide by 0 (Divide by zero error encountered.)
BEGIN TRY
 EXEC testPrc '0'
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Test for Datatype conversion (Conversion failed when converting the varchar value 'a' to data type int.)
BEGIN TRY
 EXEC testPrc 'a'
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Test nested TRY-CATCH for "Divide by 0" & "Datatype conversion" errors both.
BEGIN TRY
 EXEC testPrc 'a'
END TRY
BEGIN CATCH
 SELECT 'outer block',
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE

 BEGIN TRY
 SELECT 1/0 AS operation
 END TRY
 BEGIN CATCH
 SELECT 'inner block',
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
 END CATCH

END CATCH
GO

--// Test for violation of PK Constraint (Violation of PRIMARY KEY constraint 'PK__testTable__2C3393D0'. Cannot insert duplicate key in object 'dbo.testTable'.)
BEGIN TRY
 CREATE TABLE testTable (a INT PRIMARY KEY)

 INSERT INTO testTable VALUES(1)
 INSERT INTO testTable VALUES(1)
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

SELECT * FROM testTable -- Contains single record with value 1

--// Test for recreating a table that already exists (There is already an object named 'testTable' in the databASe.)
BEGIN TRY
 CREATE TABLE testTable (a INT PRIMARY KEY)
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Test for inserting NULL value on Primary Key column (Cannot insert the value NULL into column 'a', table 'tempdb.dbo.testTable'; column does not allow nulls. INSERT fails.)
BEGIN TRY
 INSERT INTO testTable VALUES(NULL)
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Final Cleanup
DROP TABLE     testTable
DROP PROC testPrc
GO

MS BOL Links for TRY-CATCH:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms179296%28v=SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms179495.aspx (Error Information)

C++ Program to implement Exception Handling by using TRY CATCH – Q28

January 28, 2010 Leave a comment

Q28. Program to implement Exception Handling by using TRY CATCH:

Write a Boolean function that returns TRUE/FALSE if the unsigned int argument passed to it is a Leap Year or Non Leap Year. The function must throw an “Out Of Range” exceptio if its argument does not lie between 0 and 2100.

… from College notes (BCA/MCA assignments):

#include <iostream.h>
#include <conio.h>

enum boolean{false, true} bool;

class Cyear{
	private:
		int dd, mm, yy;
	public:
		void getDate();
		int isLeap();
		void putDate();
	};

void Cyear :: getDate(){
	cout<<"\n Enter Date (dd mm yyyy): ";
	cin>>dd>>mm>>yy;
	try{
		if( (yy < 0) || (yy > 2100) )
			throw "Out of Range";
		}
	catch(char *errmsg){
		cout<<"\n ERROR: "<<errmsg;
      }
	}

int Cyear :: isLeap (){
	return ( (yy % 400 == 0) || ((yy % 4 == 0) && (yy % 100 != 
    0)) );
	}

void Cyear :: putDate(){
	if(isLeap())
		cout<<"\n Is a leap year.";
	else
		cout<<"\n Is not leap year.";
	}

void main(){
	clrscr();
	Cyear Odt;
	Odt.getDate();
	Odt.putDate();
	getch();
   }

 

Output:

Enter a Date (dd mm yyyy): 18 11 2004

Is a Leap Year.

Enter a Date (dd mm yyyy): 18 11 2290

ERROR: Out of Range.

Is not a Leap Year.