Can anyone provide suggestions/recommendations of using either the
sqltransaction class for ado.net or a sql server stored procedure.  we need
to using begin and end transactions to update mulitple tables?

asp.net app with sql server 2000 database

Using transactions in stored procedures is strongly advised whatever it is
that you have to do. Especially if any of the following apply:

- many users;

- large database (many relational tables with plenty of records);

- the need for server-side data processing (if you need to do
data-processing from within the procedure, rather than to drag tons of data
from the server to the client for some light-weight processing).

On top of that you should use ado.net transactions in cases when one-to-many
relationships should be processed within a single coherent step.

Example:
Order <-- Item (many items in a single order)
Any one purchase may contain several products. Since your stored procedures
should manage single inserts, using the ado.net transaction is strongly
advised. The order will be placed only if all items purchased are stored.

An illustration:

ado.net transaction begins

       order transaction begins

            order is inserted

       order transaction ends (commits)

       first item transaction begins

             first item is inserted

       first item transaction ends (commits)

       ...

       last item transaction begins

             last item is inserted

       last item transaction ends (commits)

ado.net transaction ends (is committed)

This way if any of the nested transactions should fail, you still have
control of the top transaction, so no order is stored, that misses items.


ML

Could you point me to some good examples on how to use begin and end
transaction statements to insert or update mutliple tables in a stored
procedure?

Show quoteHide quote

A stored procedure provides implicitly a transaction.

If you need finer control, you can put Begin Tran, Commit Tran, and Rollback
Tran inside the procedure.

hth,

Show quoteHide quoteone-to-many
Show quoteHide quote

YOU ARE WRONG!!!!! A stored procedure does NOT run in the context of a
transaction.  You must explicitly start a transaction within a stored
procedure.  A trigger runs in the context of a transaction, because it
executes as part of a single DML statement which is automatically bracketed
by a transaction.


Show quoteHide quote

Try the following procedure:

SET NOCOUNT ON
GO
CREATE PROC testProc AS SELECT @@TRANCOUNT AS NoBeginTran
GO
EXEC testProc
GO
DROP PROC testProc
GO
CREATE PROC testProc AS BEGIN TRAN SELECT @@TRANCOUNT AS BeginTran ROLLBACK
TRAN
GO
EXEC testProc
GO
DROP PROC testProc

RESULTS:

NoBeginTran
-----------
0

BeginTran
-----------
1

Notice that @@TRANCOUNT is 0 in the first proc.  That means that no
transaction exists.



Show quoteHide quote

If you follow the basic principles of transactions, described in BOL, you
can't go wrong.

Make sure you do the following:

1) begin a transaction when needed - not too soon, not too late

If your procedure receives any parameters (as they usually do :)), and you
need to test their values, do so before you begin the transaction. If any of
these tests involve tables in which changes are frequent, those tests should
be done inside the transaction (preferably right after the begin transaction
statement).

2) check for errors before moving on

Plan your parameter tests in a way that you can end the procedure as soon as
you discover inconsistencies (for instance: if one of the parameters is
supposed to be a key of an existing record and your test for existence fails,
rollback the transaction and return an error code).
Check for errors after each insert (update) by storing the error number in a
previously declared variable right after the insert/update statement. If the
error number is not equal to 0, an error has occured and your transaction
should be rolled back and the error code returned.

3) commit transactions only after you are absolutely sure all parameter
tests were positive, and all inserts (and/or updates) have executed without
errors

After all the steps in your procedure have been executed correctly, you
should commit changes - commit the transaction. Make sure the commit
transaction statement is the last thing that executes before procedure ends.
This does not mean that 'commit transaction' is the last line of your code.
You can place the statement mostly anywhere in your code, if it is followed
by the 'return 0' statement (as this usually means the procedure ended
appropriately).


Example:

create proc dbo.Item_Add_toOrderList
  (
  @orderNumber char(20)
  @itemNumber char(20)
  @processedBy varchar(64) = null
  )
as
declare @errorNumber

-- Test processedBy
if (@processedBy is null) set @processedBy = system_user

-- Transaction
begin tran

-- Test orderNumber (makre sure an appropriate order exists)
if not exists(select OrderID from dbo.Order where (OrderNumber =
@orderNumber))
  begin
    rollback tran
    raiserror ('Item Add failed: orderNumber %s unknown.', 16, 1,
@orderNumber)
    return -1
/*
This is the first point at which the transaction will be rolled back if one
of the parameters is erroneous.
*/
  end

-- Initialize errorNumber
set @errorNumber = 0

insert dbo.Item
  (
  orderNumber
  ,itemNumber
  ,processedBy
  ,processedOn
  )
  select @orderNumber as orderNumber
           ,@itemNumber as itemNumber
           ,@processedBy as processedBy
           ,getdate() as processedOn

-- store current error code
set @errorNumber = @@error

if (@errorNumber != 0)
  begin
    rollback tran
    raiserror ('Item Add failed: an error has occured.', 16, 1)
    return @errorNumber
/* Here the transaction is rolled-back if an error occured at insert. */
  end
else
  begin
    commit tran
    return 0
/* If no error present transaction is committed. */
  end
go


For multiple inserts (updates) simply extend this example with more
insert/update statements followed by error-tests. Once again the KIS
principle should also be followed (Keep It Simple). :)


ML

I prefer to encode all transactions in stored procedures for a number of
reasons:

(1) Deadlock minimization.  It is easier to control the order in which
tables are accessed and adjust that order if you only have to look for the
code that initiates the locks in one place.

(2) Performance.  Set based updates take less time than individual updates,
because the execution plan can be optimized based on the number of rows to
be updated.  In addition, as the updates are written, SQL Server can
aggregate the index updates.  Moreover, any triggers on the tables fire only
once.

(3) Concurrency.  Because set-based updates operate faster, using a
set-based update minimizes the length that a transaction is outstanding.  In
addition, since you have better control over the order in which tables are
accessed, blocking can be reduced.

If I have to update more than one row from a client app, I create temp
tables, insert the new values and then execute the stored procedure.  I hold
off starting the transaction until all of the rows to be updated are
prepared and in a temp table.  I then use set-based statements within the
transaction to perform the updates.


Show quoteHide quote

Can I avoid using a cursor
DECLARE CURSOR ON EXEC... (Sybase does it, so why cant SQL Server?
Aaron, Itzik - about the MIN Function
IDENTITY_INSERT
Removing Date/Time Stamp from Add Remove Data in SMS table