2

I have a transaction I want to commit but unsure if it will be rolled back if anything fails. I know I can use a TransactionScope in c# and if an error occurs everything will be rolled back. But I am not so sure about a transaction in a stored procedure. All the examples online have a roll back with the ROLLBACK keyword. What if I dont have a ROLLBACK keyword then what will happen?

CREATE PROCEDURE CreatePost
    @type INT,
    @name VARCHAR(500)
AS
BEGIN
    DECLARE @insertedId TABLE(Id INT)
    DECLARE @id INT

    BEGIN TRANSACTION

    INSERT INTO [Post] ([Name], [Type])
    OUTPUT inserted.Id INTO @insertedId
    VALUES (@name, @type)

    UPDATE [Tables] SET Ordinals =ordinals + 1
    WHERE Id = @tableId

    COMMIT
END
juharr
  • 31,741
  • 4
  • 58
  • 93
Luke101
  • 63,072
  • 85
  • 231
  • 359
  • This question is not a duplicate to the chosen post. I am merely asking if the above stored procedure will roll back automatically if an error occurs. – Luke101 Feb 17 '16 at 03:00
  • 1
    Reopened. I don't think this is a duplicate question of [this](http://stackoverflow.com/questions/31421770/using-transactionscope-around-a-stored-procedure-with-transaction-in-sql-server). – Felix Pamittan Feb 17 '16 at 03:09
  • You could have created a small test that would have given you the answer in less time than it took to ask this. Keep that in mind next time you have a question about how something simple behaves. –  Feb 17 '16 at 14:13

2 Answers2

6

Yes, transaction automatically gets rolled back if it doesnt reach COMMIT statement.

Here is a simple example

sample table 

create table tt(id int)

sample procedure

CREATE PROCEDURE CreatePost
AS
BEGIN

    BEGIN TRANSACTION

    INSERT INTO tt values (1)

    select cast('ldfsfds' as int)

    COMMIT
END

Execute the procedure and query the table

EXEC CreatePost    
select * from tt

result

0 records

The end result will be an empty table, assuming there were no records before execution of procedure.

Sateesh Pagolu
  • 9,282
  • 2
  • 30
  • 48
1

A stored procedure runs in its own connection context and the connection will close when procedure ends . As you have explicitly started a transaction, if any error happens and code does not reach to commit statement, it will rollback automatically.