top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

SQL Server Transactions and Error Handling

+2 votes
48 views
SQL Server Transactions and Error Handling
posted Jun 23, 2015 by Manikandan J

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

+1 vote

Transactions
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.
Users can group two or more Transact-SQL statements into a single transaction using the following statements:

**Begin Transaction
Rollback Transaction
Commit Transaction**

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.
Here is an example of a transaction :

USE pubs DECLARE @intErrorCode INT BEGIN TRAN UPDATE Authors SET Phone = '415 354-9866' WHERE au_id = '724-80-9391' SELECT @intErrorCode = @@ERROR IF (@intErrorCode <> 0) GOTO PROBLEM UPDATE Publishers SET city = 'Calcutta', country = 'India' WHERE pub_id = '9999' SELECT @intErrorCode = @@ERROR IF (@intErrorCode <> 0) GOTO PROBLEM COMMIT TRAN PROBLEM: IF (@intErrorCode <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATEstatements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.

Error Handling

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of@@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after any INSERT, UPDATE, orDELETE statement.

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), @title VARCHAR(20), @title_type CHAR(12)) AS BEGIN TRAN INSERT titles(title_id, title, type) VALUES (@title_id, @title, @title_type) IF (@@ERROR <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1 END INSERT titleauthor(au_id, title_id) VALUES (@au_id, @title_id) IF (@@ERROR <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1 END COMMIT TRAN RETURN 0

This kind of solution contains substantial repetition especially if your business logic requires more than two Transact-SQL statements to be implemented. A more elegant solution is to group codes into a generic error handling procedure:

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), @title VARCHAR(20), @title_type CHAR(12)) AS BEGIN TRAN INSERT titles(title_id, title, type) VALUES (@title_id, @title, @title_type) IF (@@ERROR <> 0) GOTO ERR_HANDLER INSERT titleauthor(au_id, title_id) VALUES (@au_id, @title_id) IF (@@ERROR <> 0) GOTO ERR_HANDLER COMMIT TRAN RETURN 0 ERR_HANDLER: PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1
answer Jun 25, 2015 by Shivaranjini
Similar Questions
+4 votes

how to check if table exist and if it doesnt exist create table in sql server 2008

+2 votes

I have two tables to join with a column (say emp_id).. if emp_id in both the tables have null values, how will SQL Server and Oracle treat???

I read that informatica will neglect the NULL rows when joining..if I handle the null, by substituting -1, a cross-join will happen which i don't want..

What can I do here? (I cannot completely neglect the rows which has NULL)

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...