是否可以在不使用游标的情况下对集合执行存储过程? [英] Is it possible to execute a stored procedure over a set without using a cursor?
问题描述
我想对集合中的每一行执行一个存储过程,而不使用像这样的游标:SELECT EXEC dbo.Sproc @Param1 = Table1.id
从表 1
我在 SQL Server 2005 中使用 T-SQL.我认为这可能使用函数,但如果可能,我想使用存储过程(公司标准)
I would like to execute a stored procedure over each row in a set without using a cursor with something like this:
SELECT EXEC dbo.Sproc @Param1 = Table1.id
FROM Table1
I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I'd like to use a stored procedure if possible (company standards)
推荐答案
10 次中有 9 次您可以在没有游标或 while 循环的情况下做您想做的事.但是,如果您必须使用一个,我发现 while 循环往往更快.
9 out of 10 times you can do what you want without a cursor or a while loop. However, if you must use one, I have found that while loops tend to be faster.
另外,如果你不想删除或更新表,你可以使用这样的:
Also if you do not want to delete or update the table, you can use something like this:
DECLARE @id [type]
SELECT @id = MIN([id]) FROM [table]
WHILE @id IS NOT NULL
BEGIN
EXEC [sproc] @id
SELECT @id = MIN([id]) FROM [table] WHERE [id] > @id
END
这篇关于是否可以在不使用游标的情况下对集合执行存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!