sqlserver store procedure

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=@DOWN_SCORE_2 and @PERSON_RESULT=@DOWN_SCORE_3 and @PERSON_RESULT=@DOWN_SCORE_4) and (@PERSON_RESULT=@DOWN_SCORE_5) and (@PERSON_RESULT

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

转载于:http://blog.itpub.net/91551/viewspace-1005634/