调试存储过程(并编写易于调试的sproc)的最佳方式是什么?

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

创建能够减少调试痛苦的sprocs的好方法是什么?有什么工具可以调试存储过程?

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.

也许最重要的是，有什么迹象表明在sproc中发生了错误，而不是在代码中?我希望我在这里不是太糟糕。以上任何一项的答案。谢谢。

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

对于它的价值，我在。net环境中工作，SQL server。

13 个解决方案

#1

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

我在存储过程中使用的一种技术使它们在SQL Server 2005过程中更容易调试(没有IDE或调试器):

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

我在过程的参数列表末尾添加了一个名为@Debug = 0(默认为0 = off)的输入参数。

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

然后添加if (@Debug = 1)打印'…';

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

是的，让您“遍历代码”的“老派”和调试器非常棒——但是这适用于任何SQL工具(包括任何没有相同IDE的调试器)中的任何人。

Ron

罗恩

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

在过程的末尾——这取决于调用应用程序从过程调用中记录输出的情况，但我编写的应用程序将它全部记录下来。:-)

#3

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

我强烈建议您看看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.

主要的要点是，输入sql查询来执行存储过程，而不是按下F5或按下命令，按下播放键，然后使用F10和F11进入到存储的proc中。

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

非常方便——而且似乎没有人使用它。

#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.

这是一个SQL Server的单元测试框架。这并不是一个典型的调试工具，但它确实允许您为存储过程编写单元测试，这对识别错误和验证预期行为有极大的帮助。

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.

例如，如果您有一个错误存储的proc，那么您可以编写一些单元测试来了解它是如何失败的。此外，如果您对SQL代码做了更改，您可以验证您的更改没有破坏任何其他内容，或者至少告诉您问题所在。

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.

如果某个东西很难测试，那么它可能是一个很好的迹象，表明您存储的proc可能做得太多，如果它被分解为更多的焦点和目标proc，那么它可能会受益。从长远来看，这些过程应该变得相对容易调试和维护。

#5

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.

我注意到许多关于使用不同环境和技术调试SQL procs的建议，但是没有人提到DBFit。如果你不熟悉Fit和FitNesse，那就帮你自己找找看。使用这三种工具，您可以快速地为自己构建一套完整的验收测试，这将使您安心地知道您可以不受惩罚地重构。

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.

DBFit只是一系列Fit fixture，可以用来运行数据库。使用Fitness，您可以在存储的proc上编写任意多的调用排列。

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.

这不是调试本身,但很快你会惊讶你可以确定一个问题一旦你整个电池的测试对单个存储proc。失败的测试会让你直接的问题,给你具体的上下文失败所以没有想工作。最重要的是，您现在可以毫无顾虑地重构存储的proc，因为您只需重新运行测试，以确保没有破坏任何东西。

#6

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.

对于工具,您可以使用Visual Studio调试SP。如果存储过程长期以来的逻辑,你可以重构,创建单独的存储过程,并从主存储proc叫它。这将有助于缩小您的测试,并缓解你找到查询的哪个部分是错误的。

#7

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:

这可能是一个个人偏好，但我发现很难读到所有被打到一条长线上的SQL查询。我喜欢以下的压痕样式:

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.

这个简单的实践在为一个全新的数据库模式编写存储过程时帮助了我很多。通过将语句拆分为多个行，可以更容易地识别查询中错误的罪魁祸首。例如，在SQL Server Management Studio中，给出了异常的行号，因此您可以更快地锁定问题代码。

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.

对你的开发伙伴要宽容……不要把一个SQL查询的800个字符塞到一行中。如果数据库字段名或数据类型发生更改，并且没有人给您发电子邮件，您将感谢自己。

#8

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.

在执行动态SQL执行时，测试或诊断模式非常有用。确保您能够看到将要执行的内容。如果您有需要(或应该)检查错误的区域，请考虑将日志记录到具有足够详细信息的表中，这样您就可以诊断发生了什么。

#9

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.

您可以使用Sql server调试，但是我发现，除了最直接的情况(在本地服务器上调试等等)，这是非常痛苦的。我还没有找到比print语句更好的东西，因此我将感兴趣地监视这个线程。

#10

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.

我把重要的一页打碎了20分钟，才发现是我匆忙修改的存储过程。

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
*************************************/

#11

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.

这可能不是您正在寻找的答案，但是如果您已经在。net环境中，LINQtoSQL已经大大减少了我编写/使用/需要调试的存储过程的数量。

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

调试SQL的困难是LINQ中编程业务逻辑是我新的首选实践的原因之一。

#12

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)

SQL Server 2008 Management Studio的集成调试器使逐步调试变得非常容易(与解决如何使VS2005 + SQL进行调试所需的柔道相比)

#13

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.

与Ron的日志记录类似，我们通过所有其他存储过程调用日志记录proc，以帮助获取所有调用的跟踪。在整个过程中使用一个通用的批处理，以允许跟踪特定的批处理运行。这可能不是性能最好的过程，但它确实有助于追踪故障。它也非常简单地编译摘要报告给电子邮件管理员。

ie.

ie。

Select * from LogEvent where BatchId = 'blah'

Sample Call

示例调用

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

Main Proc

主要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.

继续使用CLR，看看通过SQL调用Log4Net之类的东西是很好的。由于我们的应用程序代码使用Log4Net，所以最好将进程的SQL端集成到相同的基础结构中。

#1