Re: multiple SP within transaction

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 08:24:34 -0400

A transaction can span multiple batches, and multiple stored procedures.
Parameters are passed in the standard manner, there is no difference when
using a transaction.
ie
begin tran
exec sp1
exec sp2
commit tran

of course you need to do error checking. 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leon" <vnality@msn.com> wrote in message
news:elgd1XesEHA.3320@TK2MSFTNGP15.phx.gbl...
> How can I run two or more stored procedures within a transaction?
>
> Do I create each stored procedure separately then create another
> stored procedure that execute them within a transaction? if so
> how would I pass values to each parameter?
>
> Thanks
>
>
>

Relevant Pages

  • Re: Transaction Oriented Architecture (TOA)
    ... It had better be a transaction I already know about so I can record it properly and make sure your session gets credit. ... In that case the client-side Data Layer has the responsibility for mundane tasks like forming SQL queries and encoding/decoding SQL datasets. ... What I am talking about is analogous to that layer that decouples the Business Layer through an interface so the Business objects don't have to know about the specific mechanisms. ... TOA/TOP proposes the database and its application domain stored procedures are the only persistence mechanism necessary, and that the benefits of a focused, single, data-permeable gateway between application and database far exceed the benefits of O/R mappings--regardless of abstraction--and that its lightweight appearance shouldn't be dismissed as missing heavyweight kick. ...
    (comp.object)
  • Re: multiple SP within transaction
    ... >A transaction can span multiple batches, and multiple stored procedures. ... > Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • ADO.net or TSQL Transactions
    ... Should implement a transaction in both the stored procedure AND in ADO.net ... code or is doing it in one or the other good enough to protect against ... >>stored procedures, and the stored procedures are themselve quite quite ... >>protected from this by virtue of the ADO transaction object. ...
    (microsoft.public.sqlserver.programming)
  • A question of Transactions
    ... if I use and ADO transaction object to execute say 10 ... stored procedures, and the stored procedures are themselve quite quite long ...
    (microsoft.public.dotnet.framework.adonet)
  • ADO.net or TSQL Transactions
    ... Should implement a transaction in both the stored procedure AND in ADO.net ... code or is doing it in one or the other good enough to protect against ... >>stored procedures, and the stored procedures are themselve quite quite ... >>protected from this by virtue of the ADO transaction object. ...
    (microsoft.public.dotnet.framework.adonet)