How can one iterate over stored procedure results from within another stored procedure....without cursors?

Question

I'm not sure if this is something I should do in T-SQL or not, and I'm pretty sure using the word 'iterate' was wrong in this context, since you should never iterate anything in sql. It should be a set based operation, correct? Anyway, here's the scenario:

I have a stored proc that returns many uniqueidentifiers (single column results). These ids are the primary keys of records in a another table. I need to set a flag on all the corresponding records in that table.

How do I do this without the use of cursors? Should be an easy one for you sql gurus!

Answer 1

This may not be the most efficient, but I would create a temp table to hold the results of the stored proc and then use that in a join against the target table. For example:

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

Answer 2

An ugly solution would be to have your procedure return the "next" id each time it is called by using the other table (or some flag on the existing table) to filter out the rows that it has already returned

Answer 3

Insert the results of the stored proc into a temporary table and join this to the table you want to update:

INSERT INTO #WorkTable
EXEC usp_WorkResults

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

Answer 4

Use temporary tables or a table variable (you are using SS2005).

Although, that's not nest-able - if a stored proc uses that method then you can't dumpt that output into a temp table.

Answer 5

You can use a temp table or table variable with an additional column:

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

Edit: Actually, in your situation a join will be better; the code above is a cursorless iteration, which is overkill for your situation.

Answer 6

If you upgrade to SQL 2008 then you can pass table parameters I believe. Otherwise, you're stuck with a global temporary table or creating a permanent table that includes a column for some sort of process ID to identify which call to the stored procedure is relevant.

How much room do you have in changing the stored procedure that generates the IDs? You could add code in there to handle it or have a parameter that lets you optionally flag the rows when it is called.

Answer 7

You could also change your stored proc to a user-defined function that returns a table with your uniqueidentifiers. You can joing directly to the UDF and treat it like a table which avoids having to create the extra temp table explicitly. Also, you can pass parameters into the function as you're calling it, making this a very flexible solution.

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

Edit: This will work on SQL Server 2000 and up...