我的存储过程代码为:
CREATE PROCEDURE [dbo].[PR_CalculateEvaluateGrade]
-- Add the parameters for the stored procedure here
/*@count int, --存放临时表达数据记录数
@i int, --存放循环变量
@totalcount int, --存放循环记录次数
@tutorshipid int, --存放指导关系ID
@totalscore int, --存放最后的指导成绩
@teachtaskno nvarchar(50), --存放选课号
@success bit='1' output --表示对每个课头是否全部生成了评教成绩*/
@success bit='1' output --表示对每个课头是否全部生成了评教成绩
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements
SET NOCOUNT ON;
declare @count int --存放临时表达数据记录数
declare @i int --存放循环变量
declare @totalcount int --存放循环记录次数
declare @tutorshipid int --存放指导关系ID
declare @totalscore int --存放最后的指导成绩
declare @teachtaskno nvarchar(50) --存放选课号
-- Insert statements for procedure here
--创建一个临时表
create table #TempTable(ID int identity(1,1),TutorShipID int,TeachTaskNo nvarchar(50),TotalScore int)
--向临时表里插入数据
Insert into #TempTable(TutorShipID,TeachTaskNo,TotalScore) select distinct EvaluateTeach.TutorshipID,
vteachtask.no as teachtaskno,avg(evaluateteach.grade) as finalscore
from vteachtask ,vteacher ,outline,term,tutorship,lab,arrangegroup,dept,evaluateteach
where vteachtask.teacherusername=vteacher.username and vteachtask.years=term.years
and vteachtask.termturn =term.termturn and term.iscurrentterm=1
and tutorship.teacherusername=vteacher.username and tutorship.arrangegroupid=arrangegroup.id and evaluateteach.teachtaskno=vteachtask.no
group by EvaluateTeach.TutorshipID,vteachtask.NO
order by EvaluateTeach.TutorshipID,teachtaskno asc
select @count=count(1) from #TempTable --查询当前临时表的记录的数据数
if(@count>0)
BEGIN
set @i=1 --指针变量初始化
set @totalcount=0 --累计初始化
while(@i<=@count) --开始循环
BEGIN
select @tutorshipid=TutorShipID,@teachtaskno=TeachTaskNO,@totalscore=TotalScore from #TempTable where ID=@i
update EvaluateTeach set FinalScore=@totalscore where EvaluateTeach.TutorshipID=@tutorshipid and EvaluateTeach.TeachTaskNo=@teachtaskno
set @i=@i+1 --循环递增
set @totalcount=@totalcount+1 --记录加1
END
END
if(@totalcount != @count)
BEGIN
set @success=0
END
drop table #TempTable
END
GO
我的调用代码为 ：
public int CalculateEvaluateGrade()
{
int Flag = 0;
string sql = "PR_CalculateEvaluateGrade";
//新建一个数据链接
SqlConnection conn = new SqlConnection(SQLHelper.strConnectionString);
//数据连接打开
conn.Open();
//开始一个事务
SqlTransaction Trans = conn.BeginTransaction();
//开始一个命令
SqlCommand cmd = new SqlCommand(sql,conn);
//命令中有个事务
cmd.Transaction = Trans;
//命令执行是存储过程
cmd.CommandType = CommandType.StoredProcedure;
//SqlParameter success =cmd.Parameters.Add("@success", SqlDbType.Bit, 1);
//success.Direction = ParameterDirection.Output;
SqlParameter returnvalue = cmd.Parameters.Add("Return", SqlDbType.Bit, 1);
returnvalue.Direction = ParameterDirection.ReturnValue;
return Flag;
}
