exists record should not be update in same values

sp_helptext ppInsertUpdateGradeBatch_Portal

CREATE PROCEDURE [dbo].[ppInsertUpdateGradeBatch_Portal] (

@intructorID int,

@semesterID int,

@courseID int ,

@xmlStringDegreeAudit ntext,

@xmlStringGrade ntext ,

---@Module varchar(15),

@xmlWF ntext ,

@StatementType nvarchar(20) = ''

)

AS

Begin

DECLARE @hdoc int, @Return INTEGER

SET @Return=0

----------------Trans Open--------------------

BEGIN TRANSACTION

--Insert/Update tsDegreeAudit

Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlStringDegreeAudit

--IF @xmlStringDegreeAudit is null Or @xmlStringGrade is null

--GOTO Hell

Select *

from OPENXML(@hdoc,'ROOT/tsDegreeAudit',2)

WITH (DegreeAuditID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5), InstructorID INTEGER) tmpXML

--IF EXISTS (SELECT * FROM tsDegreeAudit WHERE FinalGrade=EduPlanID

--begin

UPDATE tsDegreeAudit

SET tsDegreeAudit.FinalGrade = tmpXML.FinalGrade,

tsDegreeAudit.InstructorID = tmpXML.InstructorID

FROM OpenXML(@hDoc,'ROOT/tsDegreeAudit' , 2)

WITH (DegreeAuditID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5), InstructorID INTEGER) tmpXML , tsDegreeAudit

WHERE tsDegreeAudit.EduPlanID = tmpXML.EduPlanID And tmpXML.DegreeAuditID<>-1

----end

---else

IF (@@error<>0)

GOTO Hell

INSERT into tsDegreeAudit (EduPlanID, FinalGrade,InstructorID)

SELECT EduPlanID, FinalGrade,InstructorID FROM OpenXML(@hDoc,'ROOT/tsDegreeAudit', 2)

WITH (DegreeAuditID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5),InstructorID INTEGER) tmpXML

WHERE tmpXML.DegreeAuditID=-1

IF (@@error<>0)

GOTO Hell

exec sp_xml_removedocument @hdoc

--Insert/Update ttGrade

Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlStringGrade

Select *

FROM OpenXML(@hDoc,'ROOT/ttGrade', 2)

WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(5),EduPlanID INT)TmpXML

--Case 1

UPDATE ttGrade

SET ttGrade.Grade=TmpXML.Grade

FROM OPENXML(@hdoc,'ROOT/ttGrade',2)

WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(5),EduPlanID INT)TmpXML

WHERE ttGrade.DegreeAuditID=TmpXML.DegreeAuditID and ttGrade.GradeTypeID=TmpXML.GradeTypeID And TmpXML.DegreeAuditID<>-1

IF (@@error<>0)

GOTO Hell

--Case 2 for Inserting Those Records which are in tsDegreeAudit But Not In ttGrade

INSERT INTO ttGrade (DegreeAuditID,GradeTypeID,Grade)

SELECT tsDegreeAudit.DegreeAuditID,TmpXML.GradeTypeID, TmpXML.Grade FROM OPENXML(@hdoc,'ROOT/ttGrade',2)

WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(10),EduPlanID INT)TmpXML

JOIN tsDegreeAudit on TmpXML.EduPlanID=tsDegreeAudit.EduPlanID

WHERE TmpXML.DegreeAuditID<>-1 And

TmpXML.DegreeAuditID in(select DA.DegreeAuditID from tsDegreeAudit DA) And

TmpXML.DegreeAuditID not in(SELECT DG.DegreeAuditID from ttGrade DG)

IF (@@error<>0)

GOTO Hell

INSERT INTO ttGrade (DegreeAuditID,GradeTypeID,Grade)

SELECT tsDegreeAudit.DegreeAuditID,TmpXML.GradeTypeID, TmpXML.Grade FROM OPENXML(@hdoc,'ROOT/ttGrade',2)

WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(10),EduPlanID INT)TmpXML

JOIN tsDegreeAudit on TmpXML.EduPlanID=tsDegreeAudit.EduPlanID

WHERE TmpXML.DegreeAuditID=-1

IF (@@error<>0)

GOTO Hell

exec sp_xml_removedocument @hdoc

--Insert/Update/Delete tsWFCourse

Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlWF

Select *

FROM OpenXML(@hDoc,'ROOT/tsWFCourse', 2)

WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT)TmpXML

Delete from tsWFCourse

where tsWFCourse.DegreeAuditID in

(Select DegreeAuditId From OPENXML(@hdoc,'ROOT/tsWFCourse',2)

WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT) TmpXML

Where TmpXML.Status=2)

IF (@@error<>0)

GOTO Hell

Update tsWFCourse set tsWFCourse.Grade=TmpXML.Grade

FROM OPENXML(@hdoc,'ROOT/tsWFCourse',2)

WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT)TmpXML

where tsWFCourse.DegreeAuditID=TmpXML.DegreeAuditID And TmpXML.Status = 1

IF (@@error<>0)

GOTO Hell

Insert into tsWFCourse(DegreeAuditID,Grade,EntryDate)

SELECT TmpXML.DegreeAuditID,TmpXML.Grade,Cast(GetDate() as varchar(25))

FROM OPENXML(@hdoc,'ROOT/tsWFCourse',2)

WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT)TmpXML

Where TmpXML.Status = 0

IF (@@error<>0)

GOTO Hell

exec sp_xml_removedocument @hdoc

---------------------------

if @@error=0

begin

Commit Transaction

Return 0

end

Hell:

RollBack transaction

RaisError ('Error Occured',1,1)

Return 1

End