mssql存储过程语法有关问题

2013-07-04

mssql存储过程语法问题create proc SaveRecord@LoginID int,@QuestionID int,@CouseID int,@QuestionNo in

mssql存储过程语法问题
create proc SaveRecord
        @LoginID int,
        @QuestionID int,
        @CouseID int,
        @QuestionNo int,
        @QuestionType varchar(14),
        @YourAnswer nvarchar(2000),
        @SubmitTime datetime,
        @SubmitState char(1),
        @RemainTime int,
        @ExamType varchar(7),
    @ExamTime datetime
as
--declare @RecordID int
if(EXISTS(select * from QuestionRecord where LoginID=@LoginID and CouseID=@CouseID and QuestionID=@QuestionID and ExamTime=@ExamTime and QuestionNo=@QuestionNo))
  begin
update QuestionRecord set YourAnswer=@YourAnswer,SubmitTime=@SubmitTime,SubmitState=@SubmitState,RemainTime=@RemainTime
where LoginID=@LoginID and CouseID=@CouseID and QuestionID=@QuestionID and ExamTime=@ExamTime and QuestionNo=@QuestionNo;
  end
else
  begin
insert into QuestionRecord values
(@LoginID,@QuestionID,@CouseID,@QuestionNo,@QuestionType,@YourAnswer,@SubmitTime,@SubmitState,@RemainTime,@ExamType,@ExamTime);
update StudentCouse  set HasTested='yes' where CouseID=@CouseID;
CASE @QuestionType --这样使用对不对
    WHEN 'SingleChoice'
    THEN update SingleChoice set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
    WHEN 'MutipleChoice'
    THEN update MutipleChoice set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
    WHEN 'TrueFalse'
    THEN update TrueFalse set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
    WHEN 'FillIn'
    THEN update FillIn set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
    WHEN 'SimpleQuestion'
    THEN update SimpleQuestion set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
  end MS?SQL 存储过程语法
[解决办法]
把CASE去掉换成IF，LZ有空去学习一下CASE的作用域

if XXXX then 
  UPDATE......; 
elseif XXXX then 
  UPDATE......;
........

------解决方案--------------------

try this,


create proc SaveRecord
        @LoginID int,   
        @QuestionID int,  
        @CouseID int,
        @QuestionNo int,  
        @QuestionType varchar(14), 
        @YourAnswer nvarchar(2000),  
        @SubmitTime datetime,        
        @SubmitState char(1),
        @RemainTime int,         
        @ExamType varchar(7),
        @ExamTime datetime
as
--declare @RecordID int
if(EXISTS(select * from QuestionRecord where LoginID=@LoginID and CouseID=@CouseID and QuestionID=@QuestionID and ExamTime=@ExamTime and QuestionNo=@QuestionNo))
begin 
  update QuestionRecord set YourAnswer=@YourAnswer,SubmitTime=@SubmitTime,SubmitState=@SubmitState,RemainTime=@RemainTime 
    where LoginID=@LoginID and CouseID=@CouseID and QuestionID=@QuestionID and ExamTime=@ExamTime and QuestionNo=@QuestionNo;
end
else
begin
  insert into QuestionRecord values
  (@LoginID,@QuestionID,@CouseID,@QuestionNo,@QuestionType,@YourAnswer,@SubmitTime,@SubmitState,@RemainTime,@ExamType,@ExamTime);

  update StudentCouse  set HasTested='yes' where CouseID=@CouseID;

declare @tsql varchar(6000)

select @tsql=
CASE @QuestionType --这样使用对不对
    WHEN 'SingleChoice' 
    THEN 'update SingleChoice set TotalTimes=TotalTimes+1 where QuestionID='+rtrim(@QuestionID)
    WHEN 'MutipleChoice' 
    THEN 'update MutipleChoice set TotalTimes=TotalTimes+1 where QuestionID='+rtrim(@QuestionID)
    WHEN 'TrueFalse' 
    THEN 'update TrueFalse set TotalTimes=TotalTimes+1 where QuestionID='+rtrim(@QuestionID)
    WHEN 'FillIn' 
    THEN 'update FillIn set TotalTimes=TotalTimes+1 where QuestionID='+rtrim(@QuestionID)
    WHEN 'SimpleQuestion' 
    THEN 'update SimpleQuestion set TotalTimes=TotalTimes+1 where QuestionID='+rtrim(@QuestionID)
    end

exec(@tsql)
  
end;

[解决办法]

declare @SqlT VARCHAR(500)

if(EXISTS(select * from QuestionRecord where LoginID=@LoginID and CouseID=@CouseID and QuestionID=@QuestionID and ExamTime=@ExamTime and QuestionNo=@QuestionNo))
  begin 
update QuestionRecord set YourAnswer=@YourAnswer,SubmitTime=@SubmitTime,SubmitState=@SubmitState,RemainTime=@RemainTime 
where LoginID=@LoginID and CouseID=@CouseID and QuestionID=@QuestionID and ExamTime=@ExamTime and QuestionNo=@QuestionNo; 
 
  end
else
  begin
insert into QuestionRecord values
(@LoginID,@QuestionID,@CouseID,@QuestionNo,@QuestionType,@YourAnswer,@SubmitTime,@SubmitState,@RemainTime,@ExamType,@ExamTime);
update StudentCouse  set HasTested='yes' where CouseID=@CouseID;

SET @SqlT='UPDATE '+@QuestionType+' set TotalTimes=TotalTimes+1 where QuestionID='+ltrim(@QuestionID)
EXEC(@SqlT)

[解决办法]
CASE那块改写成这样：


IF @QuestionType='SingleChoice'
BEGIN
update SingleChoice set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
END

IF @QuestionType='MutipleChoice'
BEGIN
update MutipleChoice set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
END

IF @QuestionType='TrueFalse'
BEGIN
update TrueFalse set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
END

IF @QuestionType='FillIn'
BEGIN
update FillIn set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
END

IF @QuestionType='SimpleQuestion'
BEGIN
update SimpleQuestion set TotalTimes=TotalTimes+1 where QuestionID=@QuestionID
END

热点排行

SQL Server

mssql存储过程语法有关问题