TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
veeru singh
NA
166
2.7k
exists record should not be update in same values
Jan 11 2017 1:16 AM
when the user update the exists record the same values should be update
in table. i have used open xml store procedure for update insert record.
anyone help me how to check if update same values in tables
-----here my open xml store procedure-------
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
Reply
Answers (
1
)
Difference between collection and Generic in C#
chack if date is in database