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:
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.