sql - 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