|
Home > Archive > MS SQL Server > October 2006 > Stored procedures & transactions - question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored procedures & transactions - question
|
|
| tootsuite@gmail.com 2006-10-24, 6:31 pm |
| Hi,
I have a question about transaction handling in stored procedures.
Let's say, for example, I have a stored procedure that consists of the
following stmts WITHOUT any BEGIN or END syntax:
UPDATE mytable SET...
INSERT mytable ...
What happens if the server crashes (for example), after the first
UPDATE statement completes, but before the INSERT statement runs?
Does SQL Server rollback the UPDATE statement? So, in effect, neither
statement is executed?
Thanks much
| |
| Tibor Karaszi 2006-10-24, 6:31 pm |
| BEGIN and END doesn't make a difference. If you want both or no to occur, you need to protect with
BEGIN TRANSACTION and COMMIT TRANSACTION. You also need error handling. Check out the articles at
www.sommarskog.se.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
<tootsuite@gmail.com> wrote in message news:1159217550.821119.278130@h48g2000cwc.googlegroups.com...
> Hi,
>
> I have a question about transaction handling in stored procedures.
>
> Let's say, for example, I have a stored procedure that consists of the
> following stmts WITHOUT any BEGIN or END syntax:
>
> UPDATE mytable SET...
> INSERT mytable ...
>
> What happens if the server crashes (for example), after the first
> UPDATE statement completes, but before the INSERT statement runs?
>
> Does SQL Server rollback the UPDATE statement? So, in effect, neither
> statement is executed?
>
> Thanks much
>
| |
| tootsuite@gmail.com 2006-10-24, 6:31 pm |
| Yes, this is what I thought also - someone here says otherwise. At any
rate, I'm going to test it for myself.
Tibor Karaszi wrote:[color=darkred
]
> BEGIN and END doesn't make a difference. If you want both or no to occur, you need to protect with
> BEGIN TRANSACTION and COMMIT TRANSACTION. You also need error handling. Check out the articles at
> www.sommarskog.se.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> <tootsuite@gmail.com> wrote in message news:1159217550.821119.278130@h48g2000cwc.googlegroups.com...
|
|
|
|
|