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 = (SELECT TOP 1 pfo FROM #id WHERE pfo > @pfoLoop ORDER BY pfo)
END


Easier, non? Quicker non? No cursors!

Comments

HolyTshirT said…
You should also look at Common Table Expressions (CTE)
http://msdn.microsoft.com/en-us/library/ms186243.aspx came out in SQL2005, can help out a lot in these scenarios. Much quicker too.
6 August 2009 at 01:43
Lee Barnard said…
Interesting! I haven't explored CTEs to any great degree, I've mainly used them for the old recursion/self-referencing table chestnut, oh, and partitioning by row number to generate grouped aggregates...which is extremely powerful...hmm, perhaps another blog post about that is in order.
22 October 2009 at 01:07
Post a Comment

Popular posts from this blog

Spotify analogy doesn't work

Image
So, why do I hate this so much. It looks like a great idea, it really does - what use is *just* a wheel? Or a car without a steering wheel? All the stages of development seem reasonable.  But there's a problem. What were the requirements? A car meets a very wide range of requirements, a skateboard just a few specific ones.  What if the most important requirement was to be able to carry 3 large suitcases and 4 people 200 miles?  What's the first "prototype" there? What's the first step then?  It's beguiling and misleading to present this as it's SUCH a narrow focus of what the requirements are and what the customer will actually be able to use as the product develops. Interestingly the picture is called "prototype".  And the end product is a car. Find me ONE car maker whose first prototype is a skateboard. So how do car manufacturers prototype?  They may create the basic chassis with no shell and make sure that the manufacturing process w
Read more

NDepend and Cruise Control .Net

Well, it had to happen, I finally got around to posting my fix/hack/dirty for this problem. 1. The problem: NDepend html results are very pretty, but don't work when you integrate NDepend into CC.Net, CC.Net's own xslt log parsers mean that the pretty images created by NDepend (dependency diagrams, Zone diagram, etc) are not shown. 2. The fix! Create new file called image.ashx (ashx = cut-down webpage without the tedious events wired up) Stick this in there: <%@ webhandler language="C#" class="ImageHandler" %> using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Drawing.Imaging; using System.Globalization; using System.IO; using System.Net; using System.Web; using System.Web.Caching; public class ImageHandler : IHttpHandler { public bool IsReusable { get { return true; } } public void ProcessRequest(HttpContext ctx) { try { string name = ctx.Request.QueryString["name
Read more