如何在另一个存储过程中迭代存储过程结果，而不使用游标？

Question

我不确定这是不是应该在T-SQL中完成的事情，而且我很确定在这个上下文中使用“迭代”一词是错误的，因为你永远不应该在SQL中迭代任何东西。它应该是一个基于集合的操作，对吗？无论如何，以下是场景：

我有一个存储过程，返回许多唯一标识符（单列结果）。这些ID是另一个表中记录的主键。我需要在那个表中设置所有相应记录的标志。

如果没有使用游标，我该怎么做呢？对于你们的SQL大师来说应该很容易！

- Kilhoffer

Answer 1

这可能不是最有效的方法，但我会创建一个临时表来保存存储过程的结果，并将其与目标表进行连接。 例如：

CREATE TABLE #t (uniqueid int)
INSERT INTO #t EXEC p_YourStoredProc

UPDATE TargetTable 
SET a.FlagColumn = 1
FROM TargetTable a JOIN #t b 
    ON a.uniqueid = b.uniqueid

DROP TABLE #t

- Ben Hoffstein

---

很好！我只想在 UPDATE 后面加上 "DROP TABLE #t"。如果你使用的是 SQL Server 2005，你也可以使用一个表变量代替一个带有 INSERT INTO 的临时表 - 表变量被沙箱化，并且在进程退出时自动销毁。它们大多数情况下都更快（驻留在内存中）。 - Joe Pineda

城市传说：表变量不是存储在内存中！它们存储在tempDB中。请不要传播这个谣言！ - Mitch Wheat

@Mitch：你让我思考了一下，所以我进行了一些调查，找到了这个链接： support.microsoft.com/kb/305977 Q4 正是这个问题——表变量是内存结构吗？不是，它们也会在磁盘上创建。然后，如果有足够的内存，临时表和表变量都将在内存中处理！！！谢谢！ - Joe Pineda

Answer 2

您可以将存储过程更改为返回包含唯一标识符的表的用户定义函数。您可以直接加入到UDF并像对待表一样处理它，从而避免显式创建额外的临时表。此外，您可以在调用函数时传递参数，使其成为非常灵活的解决方案。

CREATE FUNCTION dbo.udfGetUniqueIDs
()
RETURNS TABLE 
AS
RETURN 
(
    SELECT uniqueid FROM dbo.SomeWhere
)

GO

UPDATE dbo.TargetTable 
SET a.FlagColumn = 1
FROM dbo.TargetTable a INNER JOIN dbo.udfGetUniqueIDs() b 
    ON a.uniqueid = b.uniqueid

编辑： 这将适用于SQL Server 2000及以上版本...

- AugustLights

---

不错的想法，但要记住，并非所有存储过程都可以转换为UDF，因为这些需要是确定性的，并且仅调用其他确定性的代码片段。如果Kilhoffer的SP属于这些之一，他将不得不走临时表路线。 - Joe Pineda

Answer 3

使用临时表或表变量（您正在使用SS2005）。

尽管如此，这种方法不可嵌套 - 如果存储过程使用该方法，则无法将输出转储到临时表中。

- Unsliced

Answer 4

如果您升级到SQL 2008，则可以传递表参数。否则，您将被困在全局临时表或创建一个包括某种进程ID列以标识哪个调用存储过程是相关的永久表中。

更改生成ID的存储过程有多少空间？您可以在其中添加代码来处理它，或者在调用时添加一个参数，让您可以选择性地标记行。

- Tom H.

Answer 5

将存储过程的结果插入临时表中，然后将其与您想要更新的表连接：

INSERT INTO #WorkTable
EXEC usp_WorkResults

UPDATE DataTable
  SET Flag = Whatever
FROM DataTable
INNER JOIN #WorkTable
  ON DataTable.Ket = #WorkTable.Key

- Cade Roux

Answer 6

一个不太优雅的解决方案是让你的程序每次调用时返回“下一个”ID，通过使用另一个表（或现有表上的某个标志）来过滤掉它已经返回的行。

- hamishmcn

Answer 7

您可以使用带有额外列的临时表或表变量：

DECLARE @MyTable TABLE (
    Column1 uniqueidentifer,
    ...,
    Checked bit
)

INSERT INTO @MyTable
SELECT [...], 0 FROM MyTable WHERE [...]

DECLARE @Continue bit
SET @Continue = 1
WHILE (@Continue)
BEGIN
    SELECT @var1 = Column1,
           @var2 = Column2,
           ...
    FROM @MyTable
    WHERE Checked = 1

    IF @var1 IS NULL
        SET @Continue = 0
    ELSE
    BEGIN

        ...

        UPDATE @MyTable SET Checked = 1 WHERE Column1 = @var1
    END
END

编辑：实际上，在你的情况下，使用连接（join）会更好；上面的代码是一种无需游标迭代的方法，对于你的情况来说有些过度。

- technophile