遍历 SQL Server 2008 中的行

Question

考虑表SAMPLE：

id       integer
name     nvarchar(10)

有一个名为myproc的存储过程。它只需要一个参数（即id）。

给定一个名字作为参数，查找所有名称为@nameparameter的行，并将所有这些id传递给myproc。

例如：

sample->
1   mark
2   mark
3   stu
41  mark

当参数 mark 传入时，1、2和41 应该逐个传递给 myproc。

即应该按照以下方式进行：

execute myproc 1
execute myproc 2
execute myproc 41

我无法访问myproc，也无法查看其内容。我只需要将值传递给它。

- psy

---

虽然可以这样做，但通常最好避免这样做，最好将存储过程转换为 UDF 或将其内联扩展，以执行基于集合的操作。我们需要查看“myproc”的内部信息，才能建议在此情况下可行的方法。在 SQL 中，通常应寻求基于集合的解决方案，而不是基于循环的解决方案。 - Damien_The_Unbeliever

类似的问题在这里得到了解答：https://dev59.com/CmIj5IYBdhLWcg3wEhUA#34797745 - Sandeep

@Sandeep，这是一个较旧的问题，我已经接受了解决方案。 - psy

Answer 1

如果你必须进行迭代(*), 使用专门设计用于此的结构 - cursor。虽然备受诟病，但如果它最清晰地表达了你的意图，我建议使用它:

DECLARE @ID int
DECLARE IDs CURSOR LOCAL FOR select ID from SAMPLE where Name = @NameParameter

OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
    exec myproc @ID

    FETCH NEXT FROM IDs into @ID
END

CLOSE IDs
DEALLOCATE IDs

---

(*) 最近这个答案得到了一些赞，但我觉得我应该将我的原始评论合并在这里，并添加一些通用建议：

在 SQL 中，您通常应该寻求基于集合的解决方案。整个语言都是围绕基于集合的解决方案而设计的，而优化器也是围绕使基于集合的解决方案运行良好而设计的。进一步地，我们可用于调整优化器的工具也是面向集合的 - 例如将索引应用于表。

只有在极少数情况下，迭代才是最佳方法。这些情况很少见，可以类比于杰克逊关于优化的规则 - 不要这样做 - （仅限专家）现在不要这样做。

您最好首先尝试以所有受影响行的集合为基础来制定您想要的内容 - 要实现的总体变化是什么？- 然后尝试制定一个查询来封装该目标。只有当通过这样做生成的查询不能有效执行（或者有其他组件无法处理除了逐行处理之外的任何操作时），您才应该考虑迭代。

- Damien_The_Unbeliever

---

我想使用游标逐个发送dbmail给由一组查询定义的收件人，以便我可以捕获失败。根据您回答的第二部分，这是否是一个合适的用法？ - DFTR

@DFTR - 只要您记住在此阶段无法捕获所有可能的故障，即使在SQL Server方面一切正常，电子邮件传递也不能保证。 - Damien_The_Unbeliever

是的，我考虑过了。但至少我知道它已经传到了我们的Sophos，这基本上也就是我的工作结束的地方。所以……胜利。 - DFTR

Answer 2

我刚刚声明了一个临时表 @sample，并插入了所有名字为“rahul”的行，同时取出状态列以检查行是否被迭代。然后使用 while 循环迭代临时表 @sample 中所有名字为“rahul”的行的所有 ID。

use dumme

Declare @Name nvarchar(50)
set @Name='Rahul'
DECLARE @sample table (

    ID int,
    Status varchar(500)

    )
insert into @sample (ID,status) select ID,0 from sample where sample=@name
while ((select count(Id) from @sample where status=0 )>0) 
begin
    select top 1  Id  from @sample where status=0 order by Id
    update @sample set status=1  where Id=(select top 1  Id  from @sample where status=0 order by Id) 
end

- rahularyansharma

---

你可以将ID声明为Identity(1,1)，并在循环中使用它，而无需更新它。 - Aniket Inge

Answer 3

Declare @retStr varchar(100)

select @retStr = COALESCE(@retStr, '') + sample.ID + ', '
from sample 
WHERE sample.Name = @nameparameter 
select  @retStr = ltrim(rtrim(substring(@retStr , 1, len(@retStr )- 1)))

Return  ISNULL(@retStr ,'') 

- Kushal Shah