Error Handling In SQL Server Cursors

Spit My Last Error

---

Video Summary

In this video, I delve into error handling within SQL Server cursors, a topic that recently sparked some interest on Twitter. Initially stumped by the challenge, I decided to create a simple table with numbers 0 through 10 to illustrate my point. As we navigate through the code and its execution, you’ll see firsthand how a cursor can get stuck in an infinite loop due to unhandled errors. By adding a `FETCH NEXT` statement within the catch block, I demonstrate a practical solution that allows the cursor to continue processing rows even after encountering an error. This video is not just about solving a specific problem but also serves as a reminder of the importance of robust error handling in stored procedures—especially when working with cursors.

Full Transcript

Don’t make fun of my hair. It’s not funny. It’s not amusing. It is a sad, sad reality that I’m living in, that we’re all living in, living through, hopefully living through, surviving in these trying times. So let’s talk about error handling in cursors because there was a spec a spec of interest on Twitter about seeing something about this. So this is funny because I was stumped by this. I was stumped by this and I was staring at it for a while, just not being able to figure it out. So what I’m going to do is create a table with the numbers 0 through 10 in it. So when we look at the results of just the select query, we’re going to start at 0 and kind of count up to 10. Alright, so let’s put that in the table, right? I guess we’ll select star from the table just to prove them. I hope select star isn’t too rough on this single column. Maybe I should add a computed column to stop myself from doing that. I don’t know. I’m undecided on that. 0 through 10. So that’s 11 rows altogether, right? Sort of confusing when you see 1, 0, and then 11, 10 down there. At least it is for me, but I’m kind of dumb. So, you know, I have that going for me. So what I originally started this, I was working on something.

I mean, it was a similar setup. You know, I was doing everything right, too. I was using a cursor and I was using a table variable for all the right things, right? So I was using a table variable to catch errors and I was using a cursor to iterate over something that I shouldn’t, like it was not a set-based thing that I could have just used a window function for. It was actually something that should be iterated over. It was re-enabling untrusted foreign keys and I figured, hey, what better use for a cursor than enabling foreign keys. So what I have here in my setup is some dynamic SQL. I just have this little variable in here to throw it away because I don’t want to return the table results.

I just want to look at the messages tab and show you what’s going on. Then I have this thing over here, which is going to catch this query inside of the cursor. I’ll get to that in a minute. And then I have a table that is going to catch error messages. And what I’m going to do after that is open my cursor and fetch everything into my variable here.

And then, of course, we’ll fetch status equals zero. I am going to print out my message with raise error. I’m going to execute my dynamic SQL and then I’m going to try to fetch next into my variable. variable. And I’m doing this inside of a try-catch block because, you know, working on SP underscore human events, I have gotten quite enamored with the old begin-try, begin-catch.

Because error handling is, I think, pretty valuable when you’re working with a big store procedure, right? Knowing exactly where something happened, where the error was, what the error was, like what was going on. That’s really, really valuable stuff. And I know there’s, you know, a certain amount of error handling that is just like, you know, maybe overboard.

But what the hell? You know, I like going overboard once in a while. You know why I like going overboard? Because it beats the crap out of being on a cruise ship. That’s why. So let’s, without too much further time wasting, let’s run this whole thing.

And let’s look at what happens. Now, if you go over to the Messages tab, you’re going to notice, I think, what I noticed. And, I mean, the first thing you’re going to notice is I forgot to set no count on. So we’re going to get this one row back. But the other thing is that we kept getting the same error over and over again.

We were stuck in an infinite loop. We did not just get 11 rows back because we moved on to the next one. We got, I don’t know, however many this one. I’m not counting. Are you crazy? But we kept hitting, we would keep hitting this error over and over again.

We would keep dividing by zero and we would never move on. Now, the way around this is to double up on our fetch next. And we actually need to take this right here and we need to put another version of it inside of the catch block.

And now when I run this whole thing and I declare my cursor and I step through, what am I going to get? I’m going to get a row back from my error catching table variable. And over in the Messages tab, I’m going to get this, right, where we divided by zero and we affected one row.

We got an error and then we went through and we did a bunch of stuff that actually would divide, right? And then we got this and, you know, I don’t know. We got one row effect. I don’t really know. I don’t really care.

That’s probably the select over here, right? But then back in that error catching table, we got the error number, the error severity, the error state, and the divide by zero and the error message, which is divide by zero encountered. So there we go. That’s that.

That’s how you do error handling inside of a cursor and still have your cursor make forward progress. If you only have the fetch next here, you’re just going to get stuck in an infinite loop. But if you put a fetch next in the catch block, you will catch the error and then move on.

And then you will start back here. Now, what I thought was a little weird about this is maybe like the fetch status thing, like not bailing out, but I don’t know.

Maybe I don’t understand cursors. There’s a lot I don’t understand. Maybe cursors is one of those things that I will just hopefully never understand. Or maybe I want to understand them.

I don’t know. Maybe my next training module will be all about the wonders of cursors. Wouldn’t that be fun for you? Thanks for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

If you wanna play with the code I used to try things out on your own, here it is:

CREATE TABLE zero_ten (id INT NOT NULL PRIMARY KEY);

INSERT dbo.zero_ten ( id )
SELECT 0 AS id
UNION ALL 
SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0)
FROM sys.messages AS m

SELECT * FROM dbo.zero_ten AS zt;
RETURN;

DECLARE c CURSOR LOCAL STATIC FOR
SELECT N'DECLARE @atmosphere INT; SELECT @atmosphere = 1/' + RTRIM(zt.id)
FROM dbo.zero_ten AS zt;

DECLARE @joydivision NVARCHAR(MAX) = N'';
DECLARE @errors TABLE
(
    id INT PRIMARY KEY IDENTITY,
    error_number INT,
    error_severity INT,
    error_state INT,
    error_message NVARCHAR(MAX)
);

OPEN c;
FETCH NEXT FROM c
INTO @joydivision;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        RAISERROR(@joydivision, 0, 1) WITH NOWAIT;
        EXEC sys.sp_executesql @joydivision;
        FETCH NEXT FROM c INTO @joydivision;
    END TRY
    BEGIN CATCH
        INSERT @errors ( error_number, error_severity, error_state, error_message )
        SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE();
        --FETCH NEXT FROM c INTO @joydivision;
    END CATCH;
END;

SELECT *
FROM @errors AS e;

CLOSE c;
DEALLOCATE c;
GO