in table. i have used open xml store procedure for update insert record.
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