Transaction - how to?

[PipHans]

Hi,

Just wondering...Suppose I have a couple of stored procedures that updates
some tables. How can I "wrap" these in a transaction that can be rolled back
in case of an error? I cant find any good documentation on the subject (I
assume I wanna use BEGIN TRANSACTION and COMMIT - or something similar.)

Perhaps theres even a way to automate this? In case of an error -> auto roll
back...:)

If say I have an SQL that says: "update table1 set field1=0 where id1=0".
How would that look if using transactions?

Also...any good links would be nice...:)

--
Pip

[PipHans]

PipHans wrote:

Perhaps theres even a way to automate this? In case of an error ->
auto roll back...:)

Just a thought...seems like connection.rollback cant be trusted (From
reading the Web). Would I in any case be better of just creating a stored
procedure that does the transactions?

Something like:

create procedure makeNM as
begin transaction

<update, whatever here>

if @@error <> 0 begin
rollback transaction
return -1
end
commit transaction
go

And then call this procedure from ASP...? Any thoughts?

--
Pip

[Simon Hayes]

"PipHans" <pi*****@hotmail.com> wrote in message
news:3f***********************@dtext02.news.tele.d k...

PipHans wrote:

Perhaps theres even a way to automate this? In case of an error ->
auto roll back...:)

Just a thought...seems like connection.rollback cant be trusted (From
reading the Web). Would I in any case be better of just creating a stored
procedure that does the transactions?

Something like:

create procedure makeNM as
begin transaction

<update, whatever here>

if @@error <> 0 begin
rollback transaction
return -1
end
commit transaction
go

And then call this procedure from ASP...? Any thoughts?

--
Pip

The stored procedure solution should work fine, along the lines of what
you've outlined above. Using a procedure in preference to ASP code is often
more efficient and secure. One point is that some negative return codes are
reserved for SQL Server (I think it's -1 to -99, but I'm not completely
sure), so most stored procedures are written to return 0 for success and >=1
for some error condition.

You should read up on transactions in Books Online, as it's a key area.
Also, since you need to check @@ERROR to decide if you need to roll back,
you need to know about error-handling in SQL, which is unfortunately rather
messy. This is a good source:

http://www.algonet.se/~sommar/error-handling-II.html

Simon

[PipHans]

Simon Hayes wrote:

You should read up on transactions in Books Online, as it's a key
area. Also, since you need to check @@ERROR to decide if you need to
roll back, you need to know about error-handling in SQL, which is
unfortunately rather messy. This is a good source:

[Snip]

Thank you...Very nice.

--
regards,
Pip