SQLTeam.com | Weblogs | Forums

Query help


#1

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;