I want to do a loop inside a stored procedure, such as:

FOR EACH record in (SELECT * from table)
    PASS FIELD A, B TO ANOTHER STORED PROCEDURE
END

Is this possible with 2005 now?  If yes, how?

You can use a server-side cursor, just like older versions.  Example below.
However, set-based processing usually performs better.

DECLARE @Col1 int, @Col2 int

DECLARE MyCursor CURSOR
    LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT Col1, Col2 FROM dbo.MyTable
OPEN MyCursor
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM MyCursor INTO @Col1, @Col2
    IF @@FETCH_STATUS = -1 BREAK
    EXEC AnotherProc @Col1, @Col2
END
CLOSE MyCursor
DEALLOCATE MyCursor

Show quoteHide quote

DECLARE @ColumnA <datatype>, @ColumnB <datatype>;

DECLARE foo CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT ColumnA, ColumnB FROM table;

OPEN foo;

FETCH NEXT FROM foo INTO @ColumnA, @ColumnB;

WHILE (@@FETCH_STATUS != -1)
BEGIN
    EXEC dbo.AnotherStoredProcedure @ColumnA, @ColumnB;
    FETCH NEXT FROM foo INTO @ColumnA, @ColumnB;
END

CLOSE foo;

DEALLOCATE foo;


But you can probably get around this in various ways, since processing a
table row by row is almost guaranteed to be slow.



Show quoteHide quote

Thank you both.  Works great.

Show quoteHide quote

Accumulating Aggregator?
WHY! WHY? WHY!?!?!
Deep SQL .Net integration question
any performance diff using -Between- or >= and <= ?
Spreadsheet totals