Managing Transactions in Stored Procedure

In this article, I am giving a quick overview about Managing Transactions in Stored Procedure in SQL Server. After completing this article you will understand:

How to manage Transactions in Stored Procedure in SQL Server?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about Stored Procedure and Transactions in SQL Server in my previous articles, if you have no idea about Stored Procedure and Transactions then I will recommend you to visit the article.

I hope now you are comfortable with Stored Procedure and Transactions, we can continue this article.

I would like to give a problem before we understand Transactions in Stored Procedure.

Problem- Suppose You need to transfer some money into someone else account. How can you do this?

Solution- Yes, you are correct. You can write SQL query or stored procedure to deduct the money from the person’s account that is transferring and add the deducted money into the person’s account that is receiving the money.

SQL Code-

CREATE DATABASE TEACHMESQLSERVER

USE TEACHMESQLSERVER

GO

/*Creating BANKING Table*/

IF OBJECT_ID('BANKING', 'U') IS NOT NULL

DROP TABLE BANKING

CREATE TABLE BANKING (ACCOUNT INT, NAME VARCHAR(MAX), BALANCE MONEY)

GO

INSERT INTO BANKING VALUES(1,'GHANESH',50000)

INSERT INTO BANKING VALUES(2,'PRASAD',25000)

INSERT INTO BANKING VALUES(3,'YOGITA',35000)

INSERT INTO BANKING VALUES(4,'GAUTAM',4000)

GO

/*Inserted Records*/

SELECT * FROM BANKING

GO

/*Creating FUNDSTRANSFER Stored Procedure to transfer the money from one account to another account*/

CREATE PROCEDURE FUNDSTRANSFER (@SOURCEID INT, @DESTID INT, @AMT INT)

AS

BEGIN

UPDATE BANKING SET BALANCE = BALANCE -@AMT WHERE ACCOUNT=@SOURCEID

UPDATE BANKING SET BALANCE = BALANCE+@AMT WHERE ACCOUNT=@DESTID

END

GO

/*Checking the Balance before Transfering the money, then Transfering the money using Stored Procedure and checking the Balance after transfering*/

SELECT * FROM BANKING

EXEC FUNDSTRANSFER 2,3, 5000

SELECT * FROM BANKING

GO

As you can see from the above result set, 5000 amount has been transferred from PRASAD account to YOGITA account.

What do you think; is this enough?  My answer is Big No.

Suppose in above case if SourceID or DestID is not present then it will deduct the amount from the other account or add the amount from the other account.

As you know we have 4 accounts (1-4) in our BANKING Table, in below query I am transferring 5000 RS from account 1 to account 5. Please note account 5 is not present in the table. Let’s see what happens

Let’s execute the below code to understand the problem

SELECT * FROM BANKING

EXEC FUNDSTRANSFER 1,5, 5000

SELECT * FROM BANKING

GO

As you can see from the above result output 5000 Rs has been deducted from account 1 (GHANESH) but it was not added into any other account. It happened because we were transferring the amount into account 5 which is not present.

To overcome from the problem we need to use Transaction in Stored Procedure.

How to manage Transactions in Stored Procedure in SQL Server?

To manage the Transaction first we need to identify which statement is executed and which failed; for this we will use @@ROWCOUNT function.

Let’s ALTER our Stored Procedure.

ALTER PROCEDURE FUNDSTRANSFER(@SOURCEID INT, @DESTID INT, @AMT INT)

AS

BEGIN

DECLARE @COUNT1 INT, @COUNT2 INT

BEGIN TRANSACTION

UPDATE BANKING SET BALANCE=BALANCE-@AMT WHERE ACCOUNT=@SOURCEID

SET @COUNT1=@@ROWCOUNT

UPDATE BANKING SET BALANCE=BALANCE+@AMT WHERE ACCOUNT=@DESTID

SET @COUNT2=@@ROWCOUNT

IF @COUNT1=@COUNT2

BEGIN

COMMIT

PRINT 'AMOUNT HAS BEEN TRANFERRED'

END

ELSE

BEGIN 

ROLLBACK

PRINT 'AMOUNT TRANFERED FAILED'

END

END

Let’s try to transfer amount to account 5

EXEC FUNDSTRANSFER 1,5, 5000

As you can see this time, it didn't deduct the money. Transaction checks whether amount was deducted and added to the account, if not so then it fails.

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!