ITPub博客

首页 > 数据库 > SQL Server > sqlserver store procedure

sqlserver store procedure

原创 SQL Server 作者:liyihongcug 时间:2008-06-13 13:19:28 0 删除 编辑
CREATE PROCEDURE get_count @group_id int , @num int output--//声明输出变量 AS declare @groupid int set @groupid=@group_id select @num=num from (select count(PERSON_ID) as num,TEAm_ID from EPM_DEPT_DIM group by TEAm_ID) b where TEAm_ID=@groupid GO CREATE PROCEDURE set_avg as declare m_cursor cursor for select a.PERSON_RESULT,a.PERSON_SCORE,b.flag,a.DPT_GROUP_ID,a.KPI_GROUP_ID,a.KPI_ID,a.TEAM_KPI_ID,a.EFF_DATE from EPM_IT_PERSON_FACT a,EPM_IT_TEAM_KPI_DIM b where a.DPT_GROUP_ID=b.DEPT_GROUP_ID and a.KPI_GROUP_ID=b.KPI_GROUP_ID and a.KPI_ID=b.KPI_ID and a.TEAM_KPI_ID=b.TEAM_KPI_ID and b.flag=1 declare @PERSON_RESULT float declare @PERSON_SCORE float declare @flag int declare @DPT_GROUP_ID int declare @KPI_GROUP_ID float declare @KPI_ID float declare @TEAM_KPI_ID float declare @EFF_DATE datetime declare @person_num int open m_cursor fetch next from m_cursor into @PERSON_RESULT,@PERSON_SCORE,@flag,@DPT_GROUP_ID,@KPI_GROUP_ID,@KPI_ID,@TEAM_KPI_ID,@EFF_DATE while(@@fetch_status=0) --循环开始 begin print @PERSON_RESULT print @PERSON_SCORE print @DPT_GROUP_ID print @KPI_GROUP_ID print @KPI_ID print @TEAM_KPI_ID print @EFF_DATE exec get_count @DPT_GROUP_ID,@person_num output set @PERSON_RESULT=@PERSON_RESULT/@person_num set @PERSON_SCORE=@PERSON_SCORE/@person_num print @person_num print @PERSON_RESULT print @PERSON_SCORE insert into EPM_IT_TEAM_FACT values(@DPT_GROUP_ID,@KPI_GROUP_ID,@KPI_ID,@TEAM_KPI_ID,@PERSON_RESULT,@PERSON_SCORE,@EFF_DATE) fetch next from m_cursor into @PERSON_RESULT,@PERSON_SCORE,@flag,@DPT_GROUP_ID,@KPI_GROUP_ID,@KPI_ID,@TEAM_KPI_ID,@EFF_DATE end close m_cursor --//关闭游标 deallocate m_cursor --//释放游标引用 GO create procedure set_avg_score as begin exec set_score truncate table EPM_IT_TEAM_FACT exec set_avg end GO CREATE PROCEDURE set_score as declare m_cursor cursor for select a.PERSON_RESULT,b.UP_SCORE_1,b.DOWN_SCORE_1,b.UP_SCORE_2,b.DOWN_SCORE_2,b.UP_SCORE_3,b.DOWN_SCORE_3,b.UP_SCORE_4,b.DOWN_SCORE_4,b.UP_SCORE_5,b.DOWN_SCORE_5,a.DPT_GROUP_ID,a.KPI_GROUP_ID,a.KPI_ID,a.TEAM_KPI_ID,a.EFF_DATE from EPM_IT_PERSON_FACT a,EPM_IT_TEAM_KPI_DIM b where a.DPT_GROUP_ID=b.DEPT_GROUP_ID and a.KPI_GROUP_ID=b.KPI_GROUP_ID and a.KPI_ID=b.KPI_ID and a.TEAM_KPI_ID=b.TEAM_KPI_ID declare @PERSON_RESULT float declare @UP_SCORE_1 float declare @DOWN_SCORE_1 float declare @UP_SCORE_2 float declare @DOWN_SCORE_2 float declare @UP_SCORE_3 float declare @DOWN_SCORE_3 float declare @UP_SCORE_4 float declare @DOWN_SCORE_4 float declare @UP_SCORE_5 float declare @DOWN_SCORE_5 float declare @DPT_GROUP_ID float declare @KPI_GROUP_ID float declare @KPI_ID float declare @TEAM_KPI_ID float declare @EFF_DATE datetime declare @score float open m_cursor fetch next from m_cursor into @PERSON_RESULT,@UP_SCORE_1,@DOWN_SCORE_1,@UP_SCORE_2,@DOWN_SCORE_2,@UP_SCORE_3,@DOWN_SCORE_3,@UP_SCORE_4,@DOWN_SCORE_4,@UP_SCORE_5,@DOWN_SCORE_5,@DPT_GROUP_ID,@KPI_GROUP_ID,@KPI_ID,@TEAM_KPI_ID,@EFF_DATE while(@@fetch_status=0) --循环开始 begin print @PERSON_RESULT print @DPT_GROUP_ID print @KPI_GROUP_ID print @KPI_ID print @TEAM_KPI_ID print @EFF_DATE --if @Result = '' -- select @Result = convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field --else -- select @Result = @Result + ',' + convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field if (@PERSON_RESULT>=@DOWN_SCORE_1 and @PERSON_RESULT<@UP_SCORE_1) set @score=1 else if(@PERSON_RESULT>=@DOWN_SCORE_2 and @PERSON_RESULT<@UP_SCORE_2) set @score=2 else if(@PERSON_RESULT>=@DOWN_SCORE_3 and @PERSON_RESULT<@UP_SCORE_3) set @score=3 else if ((@PERSON_RESULT>=@DOWN_SCORE_4) and (@PERSON_RESULT<@UP_SCORE_4)) set @score=4 else if((@PERSON_RESULT>=@DOWN_SCORE_5) and (@PERSON_RESULT<@UP_SCORE_5)) set @score=5 else set @score=0 print @score update EPM_IT_PERSON_FACT set PERSON_SCORE=@score where DPT_GROUP_ID=@DPT_GROUP_ID and KPI_GROUP_ID=@KPI_GROUP_ID and KPI_ID=@KPI_ID and TEAM_KPI_ID=@TEAM_KPI_ID and EFF_DATE=@EFF_DATE fetch next from m_cursor into @PERSON_RESULT,@UP_SCORE_1,@DOWN_SCORE_1, @UP_SCORE_2,@DOWN_SCORE_2,@UP_SCORE_3,@DOWN_SCORE_3,@UP_SCORE_4,@DOWN_SCORE_4,@UP_SCORE_5,@DOWN_SCORE_5, @DPT_GROUP_ID,@KPI_GROUP_ID,@KPI_ID,@TEAM_KPI_ID,@EFF_DATE --//将实际ID赋给变量 end close m_cursor --//关闭游标 deallocate m_cursor --//释放游标引用 GO[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91551/viewspace-1005634/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Bean管理事务
下一篇: 宁波天一广场
请登录后发表评论 登录
全部评论
  • 博文量
    26
  • 访问量
    189648