Zebradillo
- Get link
- X
- Other Apps
Non-cursor cursors
So I've been doing SQL queries for a while now, but only just worked out that 99 times out of 100, whenever you think you need a cursor, you probably don't. You probably need some sort of loop and some sort of track of where you are...but the answer to that is NOT a cursor. For example: You have a list of portfolios that you wish to value, but you need to call a proc/function against each in turn, so doing a single query is out of the question. The answer would appear to be a cursor, iterating over a list. WRONG. Here's how NOT to do it: DECLARE pfo_cursor CURSOR FOR SELECT pfo FROM #id OPEN pfo_cursor FETCH NEXT FROM pfo_cursor INTO @pfoLoop WHILE @@FETCH_STATUS = 0 ...do stuff with @pfoLoop FETCH NEXT FROM pfo_cursor INTO @pfoLoop CLOSE DEALLOCATE And here's a quicker, easier, cheaper, better way: SET @pfoLoop = (SELECT TOP 1 pfo FROM #id ORDER BY pfo) WHILE @pfoLoop is not null BEGIN ...do stuff with @pfoLoop SET @pfoLoop = (...
Comments