遍历 SQL Server 2008 中的行 [英] Iterate through rows in SQL Server 2008

问题描述

考虑表格样本:

id       integer
name     nvarchar(10)

有一个名为 myproc 的存储过程.它只需要一个参数(即 id)

There is a stored proc called myproc. It takes only one paramater ( which is id)

给定一个名称作为参数，找到所有具有 name = @nameparameter 的行并传递所有这些 idmyproc

Given a name as parameter, find all rows with the name = @nameparameter and pass all those ids to myproc

例如:

sample->
1   mark
2   mark
3   stu
41  mark

mark 传递时，1 ,2 和 41 将分别传递给 myproc.

When mark is passed, 1 ,2 and 41 are to be passed to myproc individually.

即应该发生以下情况:

execute myproc 1
execute myproc 2
execute myproc 41

我无法触摸 myproc 也无法看到其内容.我只需要将值传递给它.

I can't touch myproc nor see its content. I just have to pass the values to it.

推荐答案

如果您必须迭代(*)，请使用专为它设计的结构 - 光标.很多人诽谤，但如果它最清楚地表达了你的意图，我会说使用它:

If you must iterate(*), use the construct designed to do it - the cursor. Much maligned, but if it most clearly expresses your intentions, I say use it:

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

<小时>

(*) 这个答案最近收到了一些赞成票，但我觉得我也应该在这里合并我的原始评论，并添加一些一般性建议:

---

(*) This answer has received a few upvotes recently, but I feel I ought to incorporate my original comment here also, and add some general advice:

在 SQL 中，您通常应该寻求基于集合的解决方案.整个语言面向基于集合的解决方案，并且(反过来)优化器面向使基于集合的解决方案运行良好.反过来，我们可用于调整优化器的工具也是面向集合的——例如对表应用索引.

In SQL, you should generally seek a set-based solution. The entire language is oriented around set-based solutions, and (in turn) the optimizer is oriented around making set-based solutions work well. In further turn, the tools that we have available for tuning the optimizer is also set-oriented - e.g. applying indexes to tables.

在少数情况下，迭代是最好的方法.这些是很少的，并且可能被比作杰克逊关于优化的规则——不要这样做——并且(仅限专家)不要这样做还没有.

There are a few situations where iteration is the best approach. These are few are far between, and may be likened to Jackson's rules on optimization - don't do it - and (for experts only) don't do it yet.

您最好首先尝试根据要影响的所有行的集合来制定您想要的内容 - 要实现的总体更改是什么?- 然后尝试制定一个包含该目标的查询.只有当这样做产生的查询没有充分执行时(或者有一些其他组件除了单独处理每一行之外无法做任何事情)你才应该考虑迭代.

You're far better served to first try to formulate what you want in terms of the set of all rows to be affected - what is the overall change to be achieved? - and then try to formulate a query that encapsulates that goal. Only if the query produced by doing so is not performing adequately (or there's some other component that is unable to do anything other than deal with each row individually) should you consider iteration.

这篇关于遍历 SQL Server 2008 中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！