Transactions In Stored Procedure

• Ashwin9

  SSC-Addicted

  Points: 477

  More actions

  July 6, 2011 at 5:03 am

  #148256

  Hi Friends,

  I've three tables viz : Classes, Students, Logs.

  Classes - Store records of one single class, no. of students present/absent etc.

  Students - Store records of multiple students with ClassId of above table.

  Logs - Store records of edited students field.

  The Stored procedure itself is understable.

  I want to add Transaction to the following procedure.

  ie add SavePoint, Begin transaction, RollBack Transaction, Trap Error, Goto statement etc.

  As I'm lil bit new to add transactions. So, I need some suggestions regarding from where to start

  transaction, rollback and commit transaction.

  CREATE PROCEDURE ClassLogs

  (

  .

  .

  .

  )

  Select @ClassCount = "Select Count(*) From Classes Table....."

  IF (@ClassCount = 0)

  EXEC @sQry = Classes_Insert "Insert into Classes Table..."

  EXEC @sQry = Students_Insert "Insert 1 Record into Students Table..."

  ELSE IF (@ClassCount > 0)

  SELECT @ClassId= "Select ClassId From Classes Table..."

  SELECT @StudentCount = "Select Count(*) From Students Table..."

  IF (@StudentCount = 0)

  EXEC @sQry = Students_Insert "Insert Remaining Records into Students Table..."

  ELSE IF (@StudentCount > 0)

  SELECT @StudentsId = "...."

  SELECT @status = "...."

  EXEC @sQry = Logs_Insert

  EXEC @sQry = Students_Update

  EXEC @sQry = Classes_Update

  Thanks In Adv.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 6, 2011 at 5:36 am

  #1348843

  duplicate post. no replies please.

  Discussion already started here

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]