Working with Transaction in Stored Procedure

vishalneeraj-24503
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 3263 red flag

Today we will see about working with Transaction in Stored Procedure.
Sometimes it's required to have a Transaction in Stored procedure.Because if we are dealing with multiple DML(Data Manipulation Language) operations in sql-server,then we must implement transaction.

If multiple statement is executing at the same time,so it's mandatory to have a Transactions.

Transaction is a 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 i.e. Success or Failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Sql statements into a single transaction using the following statements:

1). Begin Transaction
2). Rollback Transaction
3). Commit Transaction

By definition a transaction must be Atomic, Consistent, Isolated and Durable.
 

Introduction

A transaction is a unit of work that is performed against a database.If anything goes wrong with any of the grouped statements,then all changes need to be aborted.The process of reversing changes is called Rollback in SQL Server.If everything is in order with all statements within a single transaction,all changes are recorded together in the database.In SQL Server,we say that these changes are Committed to the database.Meaning that,if all the Statement or DML operations successfully done,then commit comes into place.


Objective

Working with Transactions in SQL Server.


Using the code


Above is a General or Basic Syntax of Transaction.


We can understand Transaction by an Example:-

Suppose, we create a Table named Employee_Master
as:-

Create Table Employee_Master(employee_id int,employee_name nvarchar(50),employee_code nvarchar(10),supervisor int);

Below is the screen-shot of stored procedure:-



Now,if we Execute above Stored procedure,then we will get an error.

Exec sp_insert_employee_records

Conversion failed when converting the varchar value 'kumar' to Data type int as
shown:-



Conclusion

So,when dealing with multiple statement in Sql-Server,then always use Transaction.


Page copy protected against web site content infringement by Copyscape

About the Author

vishalneeraj-24503
Full Name: vishal kumar
Member Level: Platinum
Member Status: Member,MVP
Member Since: 11/5/2013 5:58:17 AM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)