Atomicity

...if there is such a word. Here is the question: I inherited a rather
long sproc that does a lot of things to a lot of different tables.
Let's say it fails 2/3 of the way through. Does the database roll back
all the changes that were made along the way because of, say, a deadlock
or some other problem? Note that the sproc itself has no explicit
transaction declarations.
Thanks.Atomicity is a word.
Wrap the whole sproc in a transaction. If an error occurs (Check for
@.@.error), then rollback.
Remember that @.@.error resets after each statement.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.|||Unless you are using Implicit Transactions you don't have an ATOMIC sp. Is
there a Commit at the end of the proc? If not then probably not.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.sql