Query Help

• Mvs2k11

  Ten Centuries

  Points: 1223

  More actions

  September 14, 2017 at 8:55 pm

  #402486

  I have a issue with rollback transaction when the first table StudentMaster record insert successfully and in case of failure during the second table StudentDetails data insert.

  If data is valid then records are inserted successfully in both tables…

  Please help in correcting the below sample procedure to rollback transaction in case of any failure of data insert in first table…Thanks for your help in advance…

  Table1:StudentMaster

  StudentID Bigint Identity(1,1) Not null --Pkey
  Name   Varchar(60) Not Null,
  CourseStartdate datetime2(3) Not Null,
  CourseEnddate datetime2(3) Not Null,
  Createddate datetime2(3) Not Null

  Table2:StudentDetails

  StudentID Bigint Not null, -PKEy
  Studentsubcode Bigint Not Null,-Pkey
  StudentIndicator bit,
  Updatedate datetime2(3)

  XML:
  <student>
<sinfo>
  <name>Ajson</name>
  <courseStartdate>2017-07-30</courseStartdate>
  <courseEnddate>2018-07-30</courseEnddate>
</sinfo>
<sDet>
 <Studentsubcode>1</Studentsubcode>
 <Studentsubcode>2</Studentsubcode>
 <Studentsubcode>4</Studentsubcode>
 <Studentsubcode>5</Studentsubcode>
</sDet>
</student>

  Create proceure dbo.student_create
  @XML XML

  As

  SET NOCOUNT ON,set xact_abort ON;

  Begin Trans student

  BEGIN TRY

  Update StudentDetails set StudentIndicator = 1 where StudentID = providedxmlvalue

  Insert into dbo.StudentMaster (Name,CourseStartdate,CourseEnddate,Createddate)
  SELECT
  xData.value(’…/name[1]’,‘bigint’) Name,
  xData.value(’…/courseStartdate[1]’,‘datetime2(3)’) courseStartdate,
  xData.value(’…/courseEnddate[1]’,‘datetime2(3)’) courseEnddate,
  getdate()
  FROM @fileDataX.nodes(’./student/sinfo’) as
  x(xData)

  Declare @studentid bigint
  Select @studentid = @@identity

  declare @TableSudentdet table (
  Studentsubcode Bigint NOT NULL )

  Insert Into @TableSudentdet (Studentsubcode)

  SELECT
  xData.value(’…/Studentsubcode[1]’,‘bigint’) Name
  FROM @fileDataX.nodes(’./student/sDet’) as
  x(xData)

  Insert into dbo.StudentDetails (StudentID,Studentsubcode,StudentIndicator,Updatedate)
  SELECT @studentid ,Studentsubcode,0,getdate()

  Commit Trans student

  END TRY

  BEGIN CATCH
  – Execute error retrieval routine.
  Rollback trans student
  EXECUTE usp_GetErrorInfo;
  END CATCH;