SQL Server 中的存储过程递归
我遇到一种情况，我想要一个存储过程返回一个表，该表在计算过程中递归地调用自身。
不幸的是，SQL Server 没有这些，并给我一个错误，既无法声明已存在的游标，又无法嵌套和插入 exec 语句。
我可以通过使用函数来解决其中一些问题吗？还有其他更好的方法吗？
该计算本质上是递归的，因此据我所知，使用连接无法解决此问题。
编辑：为了澄清实际的计算，因为代码因其他内容而变得复杂，并且可能会使问题复杂化 -
假设表 A 有列（containerID、objID、objType、weight），表 B 有列（itemID、value）。
表 A 中的 objType 告诉您表 A 中的 objID 是容器 ID（同样在表 A 中）还是表 B 中的 itemID。
(containerID, objID) 是表 A 上的主键，表 B 上的 itemID 也是如此。
通常是容器里面会有数十到数百件物品或其他容器。希望递归深度不要超过十几级。 （猜测）计算是得到加权平均值。
I have a situation where I want to have a stored proc returning a table that calls itself recursively as part of its calculation.
Unfortunately SQL Server is having none of this and gives me an error along the lines of both being unable to declare a cursor that already exists and about not being able to nest and insert exec statement.
Could I get around some of these issues by using a function? Is there another better way to do this?
The calculation is inherently recursive in nature, so there isn't any getting around this using joins as far as I can tell.
EDIT: to clarify the actual calculation since the code is complicated by other stuff and might complicate the matter-
suppose table A has columns (containerID, objID, objType, weight) and table B has columns (itemID, value).
objType in table A tells you whether objID in table A is a containerID (again in table A) or is and itemID from table B.
(containerID, objID) is a primary key on table A as is itemID on table B.
Generally a container will have tens to hundreds of items or other containers in it. Hopefully the recursion depth isn't more than a dozen levels. (guessing) The calculation is to get a weighted average.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您提供的信息非常少，因此这里是一个猜测：尝试使用 递归查询使用通用表表达式，尝试基于集合的操作而不是游标，或者尝试使用动态 SQL。
you provide very little information, as a result here is a guess: try using Recursive Queries Using Common Table Expressions, try set based operations and not a cursor, or try using dynamic SQL.
本文提供了 7 种不同的方法来完成您想要做的事情。
http:// /www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#_Toc205129484
This article gives 7 different ways to do what you're trying to do.
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#_Toc205129484
我认为您会收到错误，因为每个递归调用可能都使用相同的游标名称，并且嵌套调用无法打开同名的游标，直到父调用关闭游标。如果可能的话，您可以使游标名称动态化，也许像
SOME_CURSOR_{$RECURSION_DEPTH}一样简单，但您可能必须将递归深度作为参数添加到过程中。我从来没有在 SQL Server 中做过这样的事情，所以我不能 100% 确定。不确定下一个/插入执行问题，尽管它可能与光标有关。
I think you get an error because the same cursor name is probably used by every recursive call, and the nested call can't open a cursor of the same name until the parent call closes the cursor. If possible, can you make the cursor name dynamic, maybe something as simple as
SOME_CURSOR_{$RECURSION_DEPTH}, and you might have to add the recursion depth as a parameter to the procedure though. I've never done anything like this in SQL Server though so I'm not 100% sure.Not sure about the next/insert exec problem, though it might be tied to the cursor.
使用 LOCAL 范围声明游标可能会解决该问题。尽管我不确定光标在递归上下文中将如何起作用。
查看这篇文章：http://msdn.microsoft.com/en-us /library/ms189238.aspx
Declaring the cursor with LOCAL scope may resolve the issue. Although I'm not sure how the cursor would act in a recursive context.
Check out this article: http://msdn.microsoft.com/en-us/library/ms189238.aspx
关键是
LOCAL术语。它每次都会在幕后生成一个单独的光标定义。The key is the
LOCALterm. It will generate a separate cursor definition behind the scenes every time.