What is the best way to debug stored procedures (a

Question

What are good methodologies for creating sprocs that reduce the pain of debugging? And what tools are out there for debugging stored procedures?

Perhaps most importantly, what are indications to look out for that errors are happening in a sproc and not in the code? I hope I'm not all over the board too terribly bad here. Votes for answers to any of the above. Thanks.

For what it's worth, I work in a .NET environment, SQL servers.

Answer 1

I have noticed a lot of suggestions on using different environments and techniques to debug SQL procs, but no one has mentioned DBFit. If you are not familiar with Fit and FitNesse then do yourself a favor and look them up. Using these three tools you can quickly build yourself an entire suite of acceptance tests that will give you peace of mind knowing you can refactor with impunity.

DBFit is simply a series of Fit Fixtures that can be used to exercise a database. Using Fitness you can write as many permutations of calls onto your stored proc as you want to create tests for.

This isn't debugging per se, but you would be amazed at how quickly you can pinpoint a problem once you have an entire battery of tests against a single stored proc. A failing test will lead you directly to the problem and give you the exact context with which it failed so there is no guess work. On top of it all, you can now refactor your stored procs without fear because you will simply have to re-run the tests to ensure you didn't break anything.

Answer 2

Here's some advice that was reiterated to me today - if you're adding a join to an important query on the production database, make sure it's safe when there is a null field in the joining table.

LEFT JOIN

I broke an important page for 20 minutes before we figured out that it was my small, rushed stored procedure change.

And make sure you test your procedures when you make a change. To do this, I like to put a simple test query in the comments of the procedure. Obvisouly, I failed to do this today :-(

/************************************
  MyProcName

  Test:
  -----
  exec MyProcName @myParam
*************************************/

Answer 3

For tools, you can use Visual Studio to debug SP. If the stored proc has long logic, you can refactor it, create separate stored proc, and call it from your main stored proc. This will help to narrow down your testing also, and ease you to find which part of the queries is wrong.

Answer 4

This may not be the answer you are looking for but if you are already in a .Net environment LINQtoSQL has greatly reduced the amount of stored procs I write/use/need to debug.

The difficulty of debugging SQL is one of the reasons programming business logic in LINQ is my new preferred practice .

Answer 5

A couple of patterns I have seen successfully used are 'diagnostic' or 'test' modes and logging.

test or diagnostic modes are useful when you are doing dynamic SQL execution. Make sure you can see what you are going to execute. If you have areas where you need (or should) be checking for errors consider logging to a table with enough details so you can diagnose what is going on.

Answer 6

TSQLUnit

This is a unit testing framework for SQL Server. Not exactly a classic debugging tool but it does allow you to write unit tests for your stored procedures which can help tremendously in identifying bugs and to validate expected behaviors.

For example, If you have a buggy stored proc then you can write some unit tests to understand how it is failing. Also, if you make a change to your SQL code you can validate that your changes did not break anything else or at least tell you where a problem lies.

If something is hard to test then it might be a good indication that your stored proc might be doing too much and could benefit if it were be broken up into more focus and targeted procs. These procs should then become relatively easier to debug and maintain in the long run.