Showing posts with label Transaction. Show all posts
Showing posts with label Transaction. Show all posts

Sunday, July 22, 2012

Stored Procedure and Transactions

Stored Procedure and Transactions

I simply overheard the subsequent statement – “I don't use Transactions in SQL as i exploit Stored Procedure“.

I simply realized that there are numerous misconceptions regarding this subject. Transactions has nothing to try and do with Stored Procedures. Let me demonstrate that with a straightforward example.

USE tempdb
GO
-- produce three check Tables
CREATE TABLE TABLE_1 (ID INT);
CREATE TABLE TABLE_2 (ID INT);
CREATE TABLE TABLE_3 (ID INT);
GO
-- produce SP
CREATE PROCEDURE TestSP
AS
INSERT INTO TABLE_1 (ID)
VALUES (1)
INSERT INTO TABLE_2 (ID)
VALUES ('a')
INSERT INTO TABLE_3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP can error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE_1;
SELECT *
FROM TABLE_2;
SELECT *
FROM TABLE_3;
GO

Now, the most purpose is: If Stored Procedure is transactional then, it ought to roll back complete transactions when it encounters any errors. Well, that doesn't happen during this case, that proves that Stored Procedure doesn't solely give simply the transactional feature to a batch of T-SQL.