BEGIN TRANSACTION vs. BEGIN

BEGIN TRANSACTION vs. BEGIN

Post by BP Margoli » Thu, 19 Dec 2002 12:13:17



Bruce,

BIG difference between "BEGIN" and "BEGIN TRANSACTION".

The former is just a scoping type of statement ... for example ...

IF 1 = 1
  BEGIN
    PRINT 'Hello, '
    PRINT '          World'
  END
ELSE
  BEGIN
     PRINT 'Goodbye,
     PRINT '               Cruel World'
  END

The latter initiates a transaction.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hello,

> I looked throgh BOL and online, but really could not find a definitive
> answer to this. What is the difference between BEGIN and BEGIN
> TRANSACTION and why would you use one over the other and in what
> circumstances?

> My understanding is there is no difference, but I know that can't be
> correct because otherwise, why would there be two choices?

> Thanks in advance,
> BD Wexler
> Programmer/Analyst

 
 
 

1. Nested transaction - SAVE vs BEGIN [TRAN]

After a lot of poking around on Google Groups, BOL and Query Analyzer, I've
finally figured out a way to make nested transactions work.  However, I'd
like to know if what I'm doing is a Good Thing(tm), and if not, why.

A post in August 2001
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OfF5...
A.1588%40tkmsftngp02) has led me to believe that you could create atomic
stored procedures that could be safely nested within one another.  I'm a fan
of object-oriented programming, even though I'm a relative newbie, so what I
did is create several internal-only (i.e. Private) stored procedures, which
I would then call from other stored procedures I would access via ASP/ADO.
Here's some pseudo-code to illustrate what I'm talking about:

Public SQL Sprocs
==================================
// Called from ASP via ADO -- Creates a new row in Forms table
// or updates the content of an existing row, depending on whether

CREATE PROCEDURE [dbo].[pForm]


AS
   SET NOCOUNT ON

   ELSE SAVE TRANSACTION SaveCustomerFormInfo

   IF (...)
      EXEC [dbo].[iForm]

   ELSE
      EXEC [dbo].[uForm]


      COMMIT TRAN SaveCustomerFormInfo
   ELSE
      ROLLBACK TRAN SaveCustomerFormInfo
GO

Private SQL Sprocs
==================================
CREATE PROCEDURE [dbo].[iForm]

AS
   SET NOCOUNT ON

   ELSE SAVE TRANSACTION SaveCustomerFormInfo

   // Call table INSERT code
   // If there are errors, popluate the error-checking variables


      BEGIN
         COMMIT TRAN SaveCustomerFormInfo

table'
   ELSE
      BEGIN
         ROLLBACK TRAN SaveCustomerFormInfo

      END
GO

CREATE PROCEDURE [dbo].[uForm]


AS
   ...
GO

Is there anything inherently wrong with this strategy for nested
transactions?   Any help, advice or critiquing would be greatly
appreciated.........

-=Tek Boy=-

2. Storing Arrays

3. BeginTrans vs Execute("begin transaction")

4. Need help accessing SQL 6.5 using ASP pages

5. Begin Transaction/Commit Transaction SQL Server 6.5

6. Migrate Access -> SQL Server: User Rights

7. BEGIN TRANSACTION COMMIT TRANSACTIOn

8. Begin and Commit transaction within another transaction.

9. Begin Transaction and Commit Transaction in ODBC...Help!!!!!

10. COMMIT TRANSACTION/BEGIN TRANSACTION

11. Access vs Approach vs Paradox vs FilePro vs Filemaker Pro

12. Begin/Commit Transaction