22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[e_OperationInform]
@UserName varchar(50) ,
@pramXml text
--@Result char(1) OUTPUT -- 返回结果
AS
BEGIN
DECLARE @idoc int
DECLARE @examName varchar(800)
DECLARE @limitMins varchar(800)
DECLARE @neecCheck varchar(800)
DECLARE @needPrompt varchar(800)
DECLARE @setAnswer varchar(800)
DECLARE @beginDate varchar(800)
DECLARE @endDate varchar(800)
DECLARE @paperId varchar(800)
DECLARE @exemId int
EXEC sp_xml_preparedocument @idoc OUTPUT, @pramXml
SET @paperId = ( SELECT *
FROM OPENXML (@idoc,'/xml/PaperId',1) WITH (name varchar(4000))
)
SET @examName = ( SELECT *
FROM OPENXML (@idoc,'/xml/ExamName',1) WITH (name varchar(4000))
)
SET @limitMins = ( SELECT *
FROM OPENXML (@idoc,'/xml/LimitMins',1) WITH (name varchar(4000))
)
SET @neecCheck = ( SELECT *
FROM OPENXML (@idoc,'/xml/NeecCheck',1) WITH (name varchar(4000))
)
SET @needPrompt = ( SELECT *
FROM OPENXML (@idoc,'/xml/NeedPrompt',1) WITH (name varchar(4000))
)
SET @setAnswer = ( SELECT *
FROM OPENXML (@idoc,'/xml/SetAnswer',1) WITH (name varchar(4000))
)
SET @beginDate = ( SELECT *
FROM OPENXML (@idoc,'/xml/BeginDate',1) WITH (name varchar(4000))
)
SET @endDate = ( SELECT *
FROM OPENXML (@idoc,'/xml/EndDate',1) WITH (name varchar(4000))
)
INSERT INTO dbo.ExamInfo
(
ExamName ,
PaperId ,
[Status] ,
LimitMins ,
BeginDate ,
EndDate ,
NeecCheck ,
NeedPrompt ,
SetAnswer ,
CreateUser
)
VALUES (
@examName ,
@paperId ,
2 ,
@limitMins ,
@beginDate ,
@endDate ,
@neecCheck ,
@needPrompt ,
@setAnswer ,
@UserName
)
SET @exemId = @@IDENTITY
SELECT *
INTO #tmp
FROM OPENXML (@idoc,'/xml/Cs',1) WITH (ClassId int,StudentId int)
EXEC sp_xml_removedocument @idoc
IF ( @exemId > 0 )
BEGIN
BEGIN TRANSACTION
INSERT INTO ExamParperStatus
(
[ExamId] ,
[ClassId] ,
[StId] ,
[Status] ,
[IsCheck] ,
[CheckTeach] ,
[CreateUser]
)
SELECT @exemId ,
t.ClassId ,
( t.StudentId ) ,
0 ,
@neecCheck ,
@UserName ,
@UserName
FROM #tmp t
INSERT INTO ExamQuestionStatus
(
[StudId] ,
[ExamId] ,
[PaperId] ,
[QuestionId] ,
[SubjectId] ,
[CreateUser]
)
SELECT m.StudentIdd ,
@exemId ,
@paperId ,
e.QuesId ,
e.SubjectId ,
@UserName
FROM #tmp m ,
ExamQuestion e
WHERE e.PaperId = @paperId
--上面的sql语句报错的话就不走下面代码
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
END
ALTER PROCEDURE [dbo].[e_OperationInform]
@UserName varchar(50),
@pramXml Text
--@Result char(1) OUTPUT -- 返回结果
AS
BEGIN
DECLARE @idoc int
DECLARE @examName varchar(800)
DECLARE @limitMins varchar(800)
DECLARE @neecCheck varchar(800)
DECLARE @needPrompt varchar(800)
DECLARE @setAnswer varchar(800)
DECLARE @beginDate varchar(800)
DECLARE @endDate varchar(800)
DECLARE @paperId varchar(800)
DECLARE @exemId int
EXEC sp_xml_preparedocument @idoc OUTPUT,@pramXml
set @paperId = (SELECT * FROM OPENXML (@idoc,'/xml/PaperId',1) WITH (name varchar(4000)))
set @examName = (SELECT * FROM OPENXML (@idoc,'/xml/ExamName',1) WITH (name varchar(4000)))
set @limitMins = (SELECT * FROM OPENXML (@idoc,'/xml/LimitMins',1) WITH (name varchar(4000)))
set @neecCheck = (SELECT * FROM OPENXML (@idoc,'/xml/NeecCheck',1) WITH (name varchar(4000)))
set @needPrompt = (SELECT * FROM OPENXML (@idoc,'/xml/NeedPrompt',1) WITH (name varchar(4000)))
set @setAnswer = (SELECT * FROM OPENXML (@idoc,'/xml/SetAnswer',1) WITH (name varchar(4000)))
set @beginDate = (SELECT * FROM OPENXML (@idoc,'/xml/BeginDate',1) WITH (name varchar(4000)))
set @endDate = (SELECT * FROM OPENXML (@idoc,'/xml/EndDate',1) WITH (name varchar(4000)))
INSERT INTO dbo.ExamInfo(ExamName,PaperId,[Status],LimitMins,BeginDate,EndDate,NeecCheck,NeedPrompt,SetAnswer,CreateUser) values(@examName,@paperId,2,@limitMins,@beginDate,@endDate,@neecCheck,@needPrompt,@setAnswer,@UserName)
set @exemId = @@IDENTITY
SELECT * into #tmp FROM OPENXML (@idoc,'/xml/Cs',1) WITH (ClassId int,StudentId int)
EXEC sp_xml_removedocument @idoc
IF(@exemId > 0)
BEGIN
BEGIN TRANSACTION
INSERT INTO ExamParperStatus ([ExamId],[ClassId],[StId],[Status],[IsCheck],[CheckTeach],[CreateUser])
SELECT @exemId,t.ClassId,(t.StudentId),0,@neecCheck,@UserName,@UserName FROM #tmp t
INSERT INTO ExamQuestionStatus([StudId],[ExamId],[PaperId],[QuestionId],[SubjectId],[CreateUser])
SELECT m.StudentId,@exemId,@paperId,e.QuesId,e.SubjectId,@UserName FROM #tmp m, ExamQuestion e WHERE e.PaperId = @paperId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
End
END