3
\$\begingroup\$

Please assume I've exhaustively tried to come up with a set-based solution to my T-SQL problem, and that I need to use a cursor. The following is the typical1 boilerplate for a T-SQL cursor:

DECLARE @myId AS INT;

DECLARE myCursor CURSOR FAST_FORWARD FOR
SELECT id FROM MyTable;

OPEN myCursor;

FETCH NEXT FROM myCursor INTO @myId; -- COPY...

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Doing the cursor work!';

    FETCH NEXT FROM myCursor INTO @myId; -- ...PASTE! :'(
END;

CLOSE myCursor;
DEALLOCATE myCursor;

On Stack Overflow it's already been asked how to avoid the duplication. Please suppose this is quite important (e.g. it's somehow unavoidable to FETCH many columns into variables). While trying to come up with a solution to DRY the two FETCH statements into one, I ended up with boilerplate similar to the OP's answer to the mentioned question:

DECLARE @myId AS INT;

DECLARE myCursor CURSOR FAST_FORWARD FOR
SELECT id FROM MyTable;

OPEN myCursor;

WHILE 1=1 -- Not very pretty, but required to keep the FETCH statement DRY.
BEGIN
    FETCH NEXT FROM myCursor INTO @myId;
    IF @@FETCH_STATUS <> 0 BREAK;

    PRINT 'Doing the cursor work!';
END;

CLOSE myCursor;
DEALLOCATE myCursor;

Now, my code review question comes down to: is there any elegant way to DRY out duplicate FETCH statements from the "default" boilerplate (1st snippet)? If my second snippet is the easiest way, then I'd love to know if there is any way to get rid of the nasty WHILE 1=1 statement? Or is it a necessary sacrifice?

*Bonus (subjective) question:

Although the WHILE 1=1 bit from the second boilerplate isn't very pretty, I think I prefer it a lot over duplicating the FETCH statement. Any insights as to why the first boilerplate is much more prominent / widespread? Am I missing a major advantage of the first snippet?


1 Taken / adapted from the 70-461 exam training kit.

\$\endgroup\$
4
\$\begingroup\$

The infinite loop idiom in languages that have boolean literals is while ( true ) but it seems the boolean type and its literals (e.g., TRUE/FALSE) have gone the way of the DO ... WHILE as far as T-SQL is concerned. This means you're stuck with true-evaluating expressions of which 1=1 is the simplest.

In your case I'd replace the start of the loop with WHILE (1=1) -- Loop until BREAK..

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.