What is the best way to debug stored procedures (and write sprocs that are easier to debug)?

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

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 2

You can use Sql server debugging, but I've found that to be a pain in anything but the most direct of situations (debugging on a local server, etc). I've yet to find something better than print statements, so I'll be monitoring this thread with interest.

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

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.

Answer 5

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 6

One technique I use in stored procedures to make them easier to debug (without IDE or debuggers) for SQL Server 2005 procedures:

I add an input parameter named @Debug = 0 (defaulted to 0 = off) at the end of the parameter list for the procedure.

I then add if (@Debug = 1) print '...';

statements in the code at key junctures to display any useful internal values etc.

Yes, it's "old school" and debuggers that let you "walk the code" are great - but this works for anyone from any SQL tool (including anyone debugging without your same IDE).

Ron

Answer 7

Another technique I use for both simple log output and debugging is to create a table variable at the top of the procedure:

   --**************************************************************************
   -- Create a log table variable to store messages to be returned to the
   -- calling application.
   --**************************************************************************
   declare @log             as table ( msg  varchar(MAX) );

then

     insert into @log values ('Inserted a new DVO Order into IRMA, order id: [' + convert(varchar(10), @@IDENTITY ) + ']');
etc.

then ...

   select msg from @log;
end

at the end of the procedure - this depends on how well the calling application logs output from your procedure call, but the app I wrote logs it all. :-)

Ron

Answer 8

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 9

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 10

SQL Server 2008 Management Studio's integrated debugger made step-wise debugging a cinch (compared judo required to figuring out how to get VS2005 + SQL to debug)

Answer 11

This may be a personal preference, but I find it extremely difficult to read SQL queries that are all slapped onto one long line. I prefer the following indentation style:

SELECT
    [Fields]
FROM
    Table
WHERE
    x = x

This simple practice has helped me out a lot when writing stored procedures for a brand new database schema. By breaking up the statements onto many lines it becomes easier to identify the culprit of a bug in your query. In SQL Server Management Studio, for example, the line number of the exception is given, so you can target problematic code much quicker.

Be easy on your fellow developers...don't cram 800 characters of a SQL query onto one line. You'll thank yourself later if a database field name or datatype changes and nobody emails you.

Answer 12

Similar to Ron's Logging we call a logging proc through all other stored procedures to assist in getting tracing on all calls. A common BatchId is used throughout to allow tracing for a certain batch run. Its possibly not the most performant process but it does help grately in tracking down faults. Its also pretty simple to compile summary reports to email admins.

ie.

Select * from LogEvent where BatchId = 'blah'

Sample Call

EXEC LogEvent @Source='MyProc', @Type='Start'
, @Comment='Processed rows',@Value=50, @BatchId = @batchNum

Main Proc

CREATE PROCEDURE [dbo].[LogEvent]
    @Source varchar(50),
    @Type varchar(50),
    @Comment varchar(400),
    @Value decimal = null,
    @BatchId varchar(255) = 'BLANK'
AS

IF @BatchId = 'BLANK'
  SET @BatchId = NEWID()

  INSERT INTO dbo.Log
    (Source, EventTime, [Type], Comment, [Value],BatchId)
  VALUES
    (@Source, GETDATE(), @Type, @Comment, @Value,@BatchId)

Moving forward it would be nice to leverage the CLR and look at calling something like Log4Net via SQL. As our application code uses Log4Net it would be advantageous to intergrate the SQL side of processes into the same infrastructure.