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

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 2

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. :-)

Answer 3

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 4

I would strongly suggest that you take a look at the built in tooling in SQL management studio.

i have written a pretty detailed blog post about it here:

http://www.diaryofaninja.com/blog/2010/11/23/debugging-sql-queries-function-amp-stored-procedures-with-sql-management-studio

basically the gist of it is that you enter you sql query to execute your stored procedure, and instead of pressing F5 or hitting the exclamation, you hit the play button and use F10 and F11 to step through and step into your stored procs.

very very handy - and no one seems to use it.

Answer 5

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 6

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.