When a stored procedure has many steps and many possible different branches, it often becomes difficult to debug. This is especially true if you are building a dynamic SQL statement which will not be known until run-time.
So how can you go about testing and debugging such procedures?
IÆm going to describe several of the techniques I use. While I do not consider my list of techniques to be definitive, it certainly should give you a place to start.
Rule 1: read the error you are being given. Often the error will tell you exactly what is wrong if you take the time to understand it. (Of course, often it will not, but then you move onto other techniques.) DonÆt forget that errors can refer to things which happen internally such as triggers as well as your code. So if it tells you that you have violated a foreign key constraint, for instance, the problem my be that you are inserting in a child table without a main record in your stored procedure or it may be happening in an untested portion of the trigger on the table you are doing an insert on. It might even be a trigger fired in a third table based on the code in the second tableÆs trigger.
When you get an error compiling a stored procedure in query analyzer, if you right click the error and choose Locate error, it will take you to the line it found the error in. This is very useful in determining the problem. However, when you are using dynamic SQL, the stored procedure may compile properly because it wonÆt know the actual SQL statement until run-time. So this technique will not always help you either.
When I am building complex stored procedures, I usually put my steps into comments and use that to structure my code. Then I write only small bits at once, compile and test each bit as I go. If you have SQL Server 2000, you can use the debug mode of Query Analyzer to go line by line and see what has actually been populated into the variables in your procedure. I rarely use this as I personally find the way they have implemented the debugger awkward. But it certainly is something you can experiment with in your own debugging. To get to the debugger in Query analyzer, right click on a stored procedure name in the Object Browser. Then choose the debug option.
Instead I often use debug statements to see if I am getting the intended intermediate results. To do this effectively, you should write your code in a particular way. First I indent every single line of real code. Then when I put in debug statements, I always put them at the far left edge of the screen. This makes it easier to find them and take them out or comment them out later when you no longer need them.
The types of debug statement I use are usually select statements. I either select a variable, the results inserted into a temp table, or a location code.
To select a variable: Select @testvariable
To select the results of a temp table Select * from #TempTable1 Of course if you only need to see a specific column(s), just select what you need. I used the * here for convenience.
To Select locations Select 1 Select 2 Etc.
If you put these statements through your code you will know immediately exactly where the code was at the time it returned the Select statement. I use these when I have multiple if statements to see what path I am following through the if statements with a particular set of data. This will tell me if I went to the statements I thought the data should execute. This technique is really handy for locating problems where the end statements are not in the right place and therefore either statements that should be executed are not or statements that shouldnÆt be executed are being executed. It also helps me to see if my test data executes all the possible branches. If I keep track of the statements executed, I can see if none of my tests execute a particular branch which helps me to find out why or make sure that portion of the code gets tested.
Another useful techniques when dealing with code which has a lot of begin and end statements is to print out the code and physically number each begin statement and find the matching end statement.
So suppose you have something like this pseudo-code:
Begin (1) Some code Begin (2) Some code End(2) Begin (3) Some code Begin(4) Some code End(4) Some code End(3)
By numbering and matching each begin to an end you can see if the begin end chunks of code are correct or where you might need to either move or add or delete a begin or end statement. Increment each begin statement by 1. For each end statement, give it the value of highest begin statement above it that you haven't already used.
By working through this example above, you can see that there are not as many end codes as begin codes and that either another one has to be added at the end or the end for begin (3) is missing. Based on the indenting, I would probably guess that the end for begin (3) is the one missing and that may be it needs to go right after the end (4) statement. To reduce the chance of these errors, I always write the end statement at the time I write the begin statement, then go back and put in the intervening code.
Another type of debug statement you can use is to use print in place of exec to see the SQL statement you are executing when you are using dynamic SQL.
Errors on dynamic SQL usually come down to incorrect spacing, incorrect use of apostrophes, quotation marks and parentheses, missing words such as æandÆ or æwhereÆ and trying to put in columns that donÆt make sense for the table you are accessing.
Often when building dynamic SQL statements, you have a very complex method for building the end code based on handling different input variables in different ways. Therefore it is critical to see what the code is that you are sending in a particular instance when you are getting an error with a particular dataset. This is why putting the dynamic parts of the code into variables and then selecting the variables to see the contents is useful.
If you have very complex procedure and the likelihood of being able to test all the possibilities is small, then you need to build in a way to debug any time an error is returned. This way, if the statement fails at some future date, the information on the failure is stored in a table and you can see exactly what happened to make it fail. To do this I set up a table called the debug table. This can be as simple as one column or can be made more elaborate so that the stored procedure failing and the date time of the failure are also stored. I recommend the latter as this is more useful when you have many stored procedures using the automated debug process and the system is in production.
So first step is to create the table you need to store the information CREATE TABLE Debug (SPName varchar (500) NOT NULL, ErrorDate datetime NOT NULL CONSTRAINT def_ErrorDate DEFAULT (getdate()), DebugInformation ntext NOT NULL, DebugID int identity (1,1) NOT NULL CONSTRAINT pk_Debug_DebugId Primary key Clustered)
At the beginning of the stored procedure declare the following variable @DebugSQL as varchar (8000) or what ever you think you need.
Now everywhere that you want to be able to track the possible errors, put in the following code If @@Error > 0 Begin insert into debug (SPName, DebugInformation) Values ('USP_TEST_PROCEDURE, æ1') insert into debug (SPName, DebugInformation) Values ('USP_TEST_PROCEDURE, @testvariable1 + æ, æ +@testvariable2) End
You can add inserts for additional variables separately or concatenate them together depending on what you want to see. The first insert inserts a number and will need to be changed to the next highest number every time you put the code in a new location in the stored procedure. This will help you see exactly where the code errrored out. Because you put a default value for the Error date in the table, it will automatically fill in that field. You could also add a field for variable name and insert the contents of all pertinent variables on separate lines.
A variant way to do this is to store the location in a variable before you run the SQL Statement. In fact you could concatenate to an existing variable each time and see the whole path through the procedure all at once And then only put in the error code inserts in a separate section at the end of the stored procedure. Then you only run each statement if @@Error =0 and the first time you hit an error it will end up at the error handler. This is a good way to do this if you are also going to commit or roll back transactions.
On the subject of using the debugger, a discussion brought up these points.
"rcampbellwhite (Programmer) Mar 16, 2004 Guys, by far the easiest method of debugging stored procedures is to spend the time getting the debugger working for you. Yes, it's sometimes frustrating to get it up and running, but with a bit of patience you will be richly rewarded because *nothing* beats being able to step through your proc, setting breakpoints, and checking variable values on the fly.
If debugging isn't working on your server, here's a good MSDN article to start you off.
You can also find a lot of info about this topic by searching for "Debugging stored procedures" in the newsgroup microsoft.public.dotnet.framework.adonet which is accessible through Google Groups.
I found this afternoon that there were two problems that were preventing my server from letting me do interactive debugging:
Firstly, using dcomcnfg (start...run...dcomcnfg), select "SQL Debugger Registry2" in the applications tab. Click properties, select the Identity tab, and then select "The Interactive User" as the account to run this application. Click ok, and click ok again to save this setting. Restart the SQL Server service.
At the very least this will provide you with a more meaningful error message if the debugger still won't start. Check the articles above for keywords from your error message and follow the advice. It will work eventually!
The second and crucial problem that I found was that my server had been renamed at some point. This is a known problem with the debugger and is covered in MSDN knowledge base article 317241.
To quickly check if your server has been renamed, type SELECT @@SERVERNAME into Query Analyzer. If you don't get the server name you expect then you may have a renamed server on your hands. See the article above for further instructions."
Thank you to rcampbellwhite for a good look at resolving problems in using the debugger and for contributing his thoughts on why you should use the debugger.