I recently put together a lesson on debugging stored procedures. When you debug stored procedures, you’re able to run their code line by line, to easily troubleshoot logic issues.
If you’re not familiar with debugging stored procedures you’ll want to watch this introductory video; as it will get you acquainted with some of the main concepts.
You can learn more about this this video and following transcript.
Debugging Stored Procedures
In this section we are going to talk about debugging stored procedures. Before we get into working through the mechanics of how to debug and start procedure, I thought we would just talk about the background and what debugging really is.
Way back in the early days when computers were first being invented, they were mechanical and one of the first issues that they had with a computer was there was actually a moth that got stuck in relay and caused the relay to stop functioning.
The First Bug
That became the first computer bug. That’s where the term “debugging” comes from because that’s where they actually found
The fault in the circuit is when they found this moth stuck in this relay. Here’s picture of that first bug and it’s from a Wikipedia article, so that’s the trivia.
As you know on computers, there’s lots of jargon, you always wonder where it comes from. This actually comes from a real live bug and there it is under Scotch tape.
You may wonder why we debug stored procedures. Reason is, is that as our SQL gets more complicated, it becomes harder to understand where an error may be.
You can imagine as our stored procedures get more and more statements, it’s going be a little tougher for us to understand where there may be a typo or maybe a logic error.
We’re going to need a way to run our statement and as we’re going through our program, maybe be able to bring out a microscope, figuratively speaking, and inspect our program as it’s running to see how it’s behaving and at certain points.
This way we can determine if the program is behaving the way we think it was supposed to behave and if not, gain a better understanding of what’s happening so we can correct any issues that we’re seeing.
Debugging Concepts
So debugging stored procedures is going to allow us to troubleshoot our much better than just kind of using blind trial and error.
So there’s some really useful debugging concepts that we’re going to dig into during our class session.
The first is Step Into. This is used to move the code execution from one procedure directly into another. The key is the execution stops, once the calling procedure executed the definition.
The second is Step Over. Here, instead of debugging the calling procedure line-by-line, we choose to just run it, and once that procedure is complete, receive control back. This way we can continue to step through our current proc without having to dig really deep into calls we don’t need to troubleshoot.
We can set up what are called Break Points, which will allow us to have our code automatically stop on a line if that line is encountered in the stored procedure.
There’s also local variables that we can inspect. We can actually see the values of our variables as the stored procedures are running.
So these are all very useful concepts that we’ll be using to help us troubleshoot stored procedures.
So in the next lesson, we’re going to actually put these concepts to use and bring them to life.
Leave a Reply