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.
About the Author
