Diagnosing Programming Errors

Answer 1

There's a lot of good writing here on SO about debugging practices, check out the tags debugging and debug. For some general questions there are "debugging techniques", "best way to debug a program", and "what's the toughest bug you've ever found and fixed?"

None of those are about debugging SQL stored procedures specifically, of course. I don't have any personal experience with that, but there seems to be a lot of articles on the web about it.

Answer 2

Stored procedures can be difficult to debug because if the execution of the CREATE/ALTER procedure returns a 'command(s) completed successfully', then your left with a logic error if something does not work... generally. Of course if your doing dynamic sql inside that SP and use executesql or something along those lines, u can break something, but those are usually fairly obvious errors.

The logic problems come down to good design and better testing of flow.

With the example provided, the extra space occured on the code side, and probably could have been trimmed before being passed as an argument to the stored procedure, this does not sound like a SP issue per say, especially when the test works in the query analyzer.

Another big problem is if you have nested SP's, and your code only sees the initial SP. THOSE are real fun to debug.

The only approach I know is to re-enact what the code is attempting and determine where the logic/syntax flaw is.

Answer 3

I think the hardest things to debug in sps are those where it doesn't error out but the answer is wrong. Far too many programmers don't really understand their data well enough to tell that the the query doesn't return the right results. There is no substitute for understanding what the right results should be. If you don't really know, talk to a functional expert; it is not good to spend time producing a large report and then when it's published, the first time it is run for real, the users come back and tell you it obviously is wrong. It takes a lot of work to functionally understand what the data means but it will pay off in spades. There is a reason why the data analyst who is deeply involved in the guts of the database for several years can find what is wrong with a query much faster than the developer who hasn't taken the time to really understand the data. When I first came to work here, I spent a long time on a report that kept getting sent back to me by our functional expert because he would look at it for ten seconds and see something that didn't make sense to him. I learned alot from that and I don't make those kinds of errors know because I understand many more of the underlying assumptions about what the data means. When functional experts have criticisms like this (and they will, let's face it they rarely tell you everything about the requirements), then file that information away for the future and start building a personal knowldgebase about the data and what it means and what it is used for. The better you understand your database structure and the better you understand what people actually need the information for, the more effectively you can query the database.

Another sneaky thing is the error message that doesn't make sense. It tells you the insert failed on a foreign key contraint but you aren't doing an insert at all, you are doing an update. The first thing to suspect in a case like this is a trigger. To avoid these things happening to you, don't write code that inserts, deletes or updates a table without first familiarizing yourself with the triggers on dthe hood. Triggers are the culprit especially when the person who wrote them didn't consider multi-row changes to the data.

In the case you cited, where a proc runs from the query window but not from the application, alawys look first at how the application is calling the proc. As you saw, sometimes it isn't sending what you think it is sending. Profiler is a great way to capture what is really being sent.

Answer 4

I generally do something like the following:

1. if someone else reports the error, you must first reproduce it.
2. once you know how to reproduce the error, write a test for the expected result (which will fail until you fix it)
3. determine the cause (debugger, logging, etc.) and fix the issue (resulting in a passing test).

Having the new test in place can help prevent the same or related error from happening again or at least catch it before being deployed to a production environment.

Most of the issues I've caused or seen result from any of the following:

1. Not testing or not having tests available to help determine if changes have impacted related code.
2. Improper assumptions about code flow, allowed value ranges, etc.
3. An environmental or dependent system down condition or change which isn't communicated or known about when the issue happened.