sp_QuickieStore Error Handling

Red Text

---

Thanks for watching!

Video Summary

In this video, I share how I implemented error handling in the SP_QuickieStore stored procedure, which is part of a series aimed at keeping things light and fun while on vacation. I dive into using `BEGIN TRY` blocks to handle errors gracefully, explaining my approach with a live demo that includes dynamic SQL for checking query store existence. I also highlight the importance of the `CURRENT_TABLE` variable, which helps in pinpointing exactly where issues occur within the procedure. The video concludes by showcasing how error messages are raised and displayed, even when dealing with dynamic SQL blocks that exceed the 2,000 character limit for `RAISERROR`. Stay tuned for the next installment, where I explore performance troubleshooting techniques using query store data, which promises to be both interesting and practical.

Full Transcript

Erik Darling here with Erik Darling Data and have another lazy bones video while I am on vacation slash location with BeerGut Magazine to talk about how I implemented error handling in SP underscore QuickieStore. This will be the second video in the series that I am going to hopefully have five of because I don’t feel like writing anything. I don’t want anything that anyone will actually comment on. So, what will end up happening? Just kidding. No one ever comments. So, no error handling would be complete or even good without a good old fashioned begin try. So this is where our story begins. It’s begin try. And I’ve done a couple few things along the way to make things a little bit easier on me. One of the things that I did was I have a little declared variable here. Declared variable here. Declared variable. Called current table. And I use this to store what action I’m currently doing in the store procedure. It makes things a little bit easier. So, if someone decides to debug this, they hit an issue, then it makes it a little bit easier for them to find an issue. And I’ll show you kind of exactly what I mean by that. I’ll do a live demo. This whole thing. And then, what I use current table for is, is stuff like this. So, whenever I start a dynamic SQL block, then I like to figure out, okay, what am I doing? I’m checking for query store existence. And then the dynamic SQL that I generate is down here.

Right? So, I know what I’m doing. And I know what the SQL is doing. I know what the intent of the SQL is. Then I can figure out if that’s, like, if this is, like, what’s going on when I hit the error handling part of things. So, let’s go all the way down to the end of the procedure. And let’s look at what happens here. Now, of course, no begin try would be complete without end try. And no end try would be complete without a begin and end catch. Of course, SQL Server would throw an error if we didn’t do anything useful.

So, I did. I tried to do something useful. Hmm. I know. First time for everything. So, what happens in the begin catch block? Well, when I was first doing this, and I admit I am not the world’s foremost practitioner in error handling. I’ve tried and had some successes and failures with it in the past.

But what I initially started doing was, oh, well, I’m going to capture, you know, the error message, error line, proc thing. You know, I read around with Erlen. Every time I have to do error handling, I go read the Erlen Summerskog articles, articles, plural, about error handling and end up, like, re-remembering learning something new every single time.

So, what I do in the catch block is, like what was just talking about, how I use that current table thing to locate where in the procedure I am. One of the first things I do is I throw an error and I use raise error and I use a string token to insert whatever text comes from that current table variable first, right? The next thing I do is I raise the offending query in raise error.

Now, I realize that there are times when this is going to get cut off, right? Like, raise error has like a 2,000 or something character limit. I think at least a couple of few of the dynamic SQL blocks in there are longer than that.

So, I realize that some of them are going to cut off. The majority of them will fit in here. If you need to see the whole thing, well, then you need to, you know, use the debug parameter, and we’ll talk about that in a later video. But then the next thing I found, something that kind of surprised me a little bit, or I don’t know if it’s, I don’t know if this is, this is how ridiculous it is.

I don’t even know if this is the first time that it surprised me. But capturing all like the error number, error line, error severity, all those like functions that get built in to help you figure out where errors are happening, didn’t throw errors reliably when dynamic SQL was involved, right?

And that really messed me up. I was like, wait a minute, how am I going to figure this out? And it turns out throw does that. The only thing you need to know about throw is that whatever happens before a throw needs to be semicoloned at the beginning.

So, let’s go way up here. And since this is guaranteed to happen really, really early on in the procedure, let’s just go and make this part fail, right? Because, you know, why not, right?

And let’s go run spquickiestore and see what happens. And this is what it looks like when the query fails for some reason, right? We get the error message up here. We see that the error, we got an error while checking for query store existence.

And that’s exactly where I removed the Y from this view so that we couldn’t find it. And then we see the offending query, right? Which is this thing, reset transaction isolation or read uncommitted.

And then we try to look for a thing that doesn’t exist here. So, that breaks completely. And then the throw, which I do at the very end, will tell us that we have an invalid object name, right?

And we can see the sysstut query store options is obviously not a thing. And we cannot query that. And so, that is how the error handling works in here.

And, you know, I just don’t have a whole lot more to say about it than that. And damn it, I’m on vacation. So, I’m going to wrap this one up.

And in the next one, we’re going to talk about something that I think is pretty neat. I don’t know how many people are actually going to use it, but I thought it was kind of a cool thing. I think I’m going to use it in other store procedures, I think. But it’s how I implemented some performance troubleshooting stuff in this proc because I always run into issues trying to get fast data from query stores.

In the next video, we’ll talk about that. Thank you for watching. Good and bye.

Bye.

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. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.