SQL Server 存储过程循环遍历结果集

SQL Server存储过程：循环遍历与计算结果集

最新推荐文章于 2022-09-06 20:04:13 发布

原创最新推荐文章于 2022-09-06 20:04:13 发布 · 1.1w 阅读

2 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

2 篇文章

订阅专栏

这是一个SQL Server的存储过程示例，用于处理任务ID为@tid的任务，并根据@ap的值更新任务的已完成百分比。过程首先检查@ap是否超过100%，如果超过则设为100%。接着，它获取任务的父任务ID，如果存在且不为0，则使用游标遍历所有子任务，累加它们的已完成百分比，并调用自身递归计算剩余部分。

USE [Task]
GO
/****** Object:  StoredProcedure [dbo].[pro_name]    Script Date: 09/22/2013 10:35:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pro_name]
    @tid int,
    @ap float
as
begin
	SET NOCOUNT ON;
	DECLARE @pid int
	DECLARE @AlreadyPercent float
	DECLARE @Percents float
	DECLARE @total float
	if(@ap > 100)
	begin
		set @ap = 100;
	end
	--更新记录
	update tbTask set AlreadyPercent = round(convert(numeric(6,3),@ap),0) where ID = @tid;
	print round(convert(numeric(6,3),@ap),0);
	SET @pid = (select top 1 tbTask.ParentTaskID from tbTask where tbTask.ID=@tid);
	
	--如果记录存在，进入循环
    IF(@pid is not null and @pid != 0)
    BEGIN
		set @total = 0;
    	DECLARE vend_cursor CURSOR
		FOR SELECT tbTask.AlreadyPercent,tbTask.Percents FROM tbTask where tbTask.ParentTaskID = @pid
		OPEN vend_cursor
		FETCH NEXT FROM vend_cursor into @AlreadyPercent,@Percents;
        
        WHILE(@@fetch_status=0)
        BEGIN
			set @total = @total + @AlreadyPercent * @Percents / 100;
            FETCH NEXT FROM vend_cursor into @AlreadyPercent,@Percents;
        END
        --print 'total = ' + cast(@total as varchar);
        close vend_cursor   --关闭游标
        deallocate vend_cursor
        exec pro_name @pid, @total;
    END
end