遍历 SQL Server 2008 中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6606709/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Iterate through rows in SQL Server 2008
提问by psy
Consider the table SAMPLE:
考虑表 SAMPLE：
id integer
name nvarchar(10)
There is a stored proc called myproc. It takes only one paramater ( which is id)
有一个名为myproc. 它只需要一个参数（即 id）
Given a name as parameter, find all rows with the name = @nameparameterand pass all those ids
to myproc
给定一个名称作为参数，找到所有带有 的行name = @nameparameter并将所有这些 id 传递给myproc
eg:
例如：
sample->
1 mark
2 mark
3 stu
41 mark
When markis passed, 1 ,2 and 41are to be passed to myprocindividually.
当mark被传递，1 ,2 and 41被传递到myproc个别。
i.e. the following should happen:
即应该发生以下情况：
execute myproc 1
execute myproc 2
execute myproc 41
I can't touch myprocnor see its content. I just have to pass the values to it.
我无法触摸myproc或看到它的内容。我只需要将值传递给它。
回答by Damien_The_Unbeliever
If you mustiterate(*), use the construct designed to do it - the cursor. Much maligned, but if it most clearly expresses your intentions, I say use it:
如果您必须迭代（*），请使用设计用于执行此操作的构造 - 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
(*) 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:
(*) 这个答案最近收到了一些赞成票，但我觉得我也应该在这里合并我的原始评论，并添加一些一般性建议：
In SQL, you should generallyseek 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 tuningthe optimizer is also set-oriented - e.g. applying indexes to tables.
在 SQL 中，您通常应该寻求基于集合的解决方案。整个语言面向基于集合的解决方案，并且（反过来）优化器面向使基于集合的解决方案运行良好。反过来，我们可用于优化优化器的工具也是面向集合的——例如将索引应用于表。
There are a fewsituations where iteration is the best approach. These are few are far between, and may be likened to Hymanson's rules on optimization - don't do it - and (for experts only) don't do it yet.
这里有几个地方的迭代是最好的办法的情况。这些都是少数是远远之间，并且可以比作Hyman逊对优化规则-不要做-和（专家只）不这样做还。
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 consideriteration.
您最好首先尝试根据要影响的所有行的集合来制定您想要的内容 - 要实现的总体更改是什么？- 然后尝试制定一个包含该目标的查询。只有当这样做产生的查询没有充分执行时（或者有一些其他组件除了单独处理每一行之外无法做任何事情），您才应该考虑迭代。
回答by rahularyansharma
I just declare the temporary table @sample and insert the all rows which have the name='rahul' and also take the status column to check that the row is iterated.and using while loop i iterate through the all rows of temporary table @sample which have all the ids of name='rahul'
我只是声明临时表@sample 并插入所有具有 name='rahul' 的行，并采用状态列来检查该行是否被迭代。并使用 while 循环遍历临时表 @sample 的所有行其中包含 name='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
回答by Kushal Shah
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 ,'')
