Why do we use rollback statement during writing a stored proceedure in MS SQL server?

Question

0.00/5 (No votes)

See more:

Would you please someone explain the Rollback statement in Stored Procedure in MS SQL Server? What happen,if we do not use this statement? And what are the benefits of using it?
Thanks.

What I have tried:

I am trying to discuss with senior developer and other online resources.

Posted 5-Mar-17 23:27pm

Ajit Kumar Pandit

Updated 5-Mar-17 23:33pm

v3

Add a Solution

Comments

Karthik_Mahalingam 6-Mar-17 5:34am

read these 2
https://msdn.microsoft.com/en-us/library/ms181299.aspx
https://msdn.microsoft.com/en-us/library/ms188929.aspx

Ajit Kumar Pandit 6-Mar-17 6:10am

Thank you Karthik.

Karthik_Mahalingam 6-Mar-17 9:03am

Hi Ajit,
in future
Always use Reply button, to post Comments/query to the user, so that the user gets notified and responds to your text.

Ajit Kumar Pandit 7-Mar-17 5:08am

Thank you. It seems like disable buttons that's why I have not noticed that. I'll do.

Karthik_Mahalingam 7-Mar-17 5:40am

cool, it will be active on hover :)

Ajit Kumar Pandit 7-Mar-17 7:24am

Yes, I have seen that. Thanks.

Karthik_Mahalingam 7-Mar-17 8:02am

welcome

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-03-05T23:33:00

ROLLBACK is part of transactions, and it's used to "throw away" all changes to the DB since the last BEGIN TRANS statement. From the start of the transaction to a rollback or commit, changes do no go directly to the "live" DB but are stored as "pending" for later.

If (for example) you are adding data to two related tables and any update fails, you would use ROLLBACK to discard all changes to both tables so that "incomplete" or "misleading" information isn't left behind. If they all succeed, you use COMMIT instead to actually update the "real tables" so other users have access to the data.

Andy Lanng · Accepted Answer · 2017-03-05T23:33:00

Solution 2

The rollback is usually within an if(error) block.

The idea is that you can wrap transactions up into transaction blocks where either all succeed or all fail. The worst situation is to have a few inserts succeed where the last one fails in a stored proc. It can really mess up your data.

If the rollback is not in an if(error) block then I guess that the author might change some data, fetch it them let it roll back but that would be very bad practice

Posted 5-Mar-17 23:33pm

Andy Lanng

Comments

Ajit Kumar Pandit 6-Mar-17 6:14am

I have got my points after reading all above explanations. Thank you Andy Lanng.

Andy Lanng 6-Mar-17 6:31am

Np - it looks like Griff got in mere seconds before me,... again >_<

Ajit Kumar Pandit 6-Mar-17 7:06am

I am happy what explanation given by both of you.

Andy Lanng 6-Mar-17 7:49am

ah no worries. It's just a joke between Griff and I. I post 0.0001% as often as Griff so I tease him for out posting me, which is inevitable ^_^

Ajit Kumar Pandit 7-Mar-17 5:09am

Hahaha... Good joke. I like these types of communications.