In Part 1 of Replacing Cursors, we look at a set-based replacement for a simple INSERT statement that uses an unnecessary cursor.
One of the things that used to drive me absolutely crazy as a developer was, when I’d have a cursor that was performing some sort of operation and would read that this is something that I can do set-based. Trouble is I couldn’t find any info on how to take that cursor and turn it into a set-based operation.
Again this was in 2005-2006 and sites like StackOverflow didn’t exist yet. If they had, it might have been a little easier. Just the same though, it’s still not easy to track down examples of how to replace a cursor; so in this chapter I’m going to show you exactly that: I’m going to take three common patterns that I used to do, as a developer, that use the cursor, and I’m going to replace them with set-based operations. I’m going to show you exactly what I’m doing and what I’m thinking, as I go through this process.
Let’s begin with a simple operation where we just take an IF ELSE value and, depending on the outcome of that condition, we then insert values, using what comes out of that. All right, let’s look at the code so, here we’ve got a DECLARE of the variables we’re going to use, and then the declaration of the cursor itself, and the SELECT statement it’s going to populate it.
We then, have the usual cursor language then OPEN FETCH NEXT and then the WHILE LOOP that takes place within the cursor; and then we got some conditional logic down here, the if and else that say “if the first name of the record coming in is ‘Hashmatcher’, then we want to change it hit points to 10, otherwise leave it alone,” and then from that point on we do the INSERT of those values that are assigned [00:02:00] to this variable. Then we do a FETCH NEXT, LOOP, CLOSE, DEALLOCATE so the cursor ends at the bottom. Now what we want to do is, get rid of all the cursor language, so we can focus on what it is we’re really trying to accomplish here. We’ll get rid of DECLARE CURSOR FOR, the OPEN, the FETCH NEXT, the WHILE, the FETCH NEXT down below, the LOOP, CLOSE, DEALLOCATE … all of that disappears, and now we get a little bit clearer a picture of what it is we’re trying to do.
Now, because we’re doing this in a set-based manner we can also get rid of the variables that we’ve declared, because we’ll be able to work with these in line; and you’ll see what that looks like in a minute. Let’s get rid of the DECLARE HP and @fname variables. All right, this is looking a little simpler. Now we have more to focus on: We’ve got our SELECT statement, we’ve got our INSERT statement, and then we have the conditional logic; from there we can keep chiseling down a little bit finer and finer. Now we’ve also got down this VALUE statement down here and because we’re doing things in a set-based manner, we don’t really need that anymore, so, we’re going to get rid of that too.
Now that we’ve gotten rid of that VALUE statement we have to figure out what we’re going to populate that INSERT with because the INSERT can’t just float down there by itself it needs something to INSERT, right? What do we have left, we have some conditional logic in the IF ELSE statement, and we have the SELECT. If we want to do this in a set-based manner then the SELECT statement seems like a pretty good option for that, doesn’t it?
We’ll take the SELECT statement and bring it down to populate the INSERT, and now we just have two components. It is getting better and better isn’t it? We got the IF ELSE statement our conditional logic, and the INSERT, driven by the SELECT. Now what we can do is find a way to get rid of that IF ELSE statement so we just have the one INSERT statement [00:04:00] and that is all. The way that we’re going to do that is to lean on an old friend from a previous chapter: We’re going to look back at using CASE.
The IF ELSE expression that we’re using here in our query is very similar to the way that the CASE expression works. We’re not doing any sort of control flow logic or any other sort of business, we’re just assigning a variable a value; and so, the transition from writing it as an IF ELSE to a CASE should be pretty straightforward. Let’s take a look at how we’re going to do that: first, file away mentally that what we want to do is, if the @fname equals ‘Hashmatcher’ set @hp to 10. All right? Now we’re going to do is get rid of that IF ELSE expression and we’re going to raise this up a little bit, so we have more room to rewrite the SELECT part now all we want to do it instead of saying SELECT HP we want to say SELECT CASE WHEN. WHEN what? WHEN FName equals ‘Hashmatcher’ THEN 10, ELSE HP … and there you go.
Now all we have to do is clip it off by saying END, and then finish the rest of our query FName FROM Source. Now, if you look at this query, it’s going to INSERT, it’s going to have our conditional logic, and it’s going to have the right values; so we manage to take that entire cursor it was about this big, and boil it down to one simple INSERT statement with a CASE nested inside. Now this was a fairly straightforward example. Let’s take a look at one that’s a little bit more complicated than the one we’ve just tackled here ..
- 01. Prologue and Script Files
- 02. Thinking in Sets (5m)
- 03. Number and Date Tables (10m)
- 04. Case Expressions (17m)
- 05. Computed Columns (10m)
- 06. Windowing Functions – Design (21m)
- 07. Windowing Functions – Performance (8m)
- 09. Replacing Cursors, Part 2 (5m)
- 10. Replacing Cursors, Part 3 (7m)
- 11. Let the Adventure Begin! (9m)
- 12. About Doug (2m)