What is the preferred method for debugging MS SQL stored procedures?

Question

Most of my SPs can simply be executed (and tested) with manually entered data. This works well and using simple PRINT instructions allows me to "debug".

However, there are cases where several stored procedures are involved, and finding valid data for input is tedious. It’s easier to just run things from my web application.

I have little experience with the profiler, but I have not found a way to study what happens line by line in my stored procedures.

What are your methods?

Thanks, as always.

Note. I assume to use SQL Server 2005 +

Answer 1

Profiler is very convenient, just add SP: StmtStarting events and filter activity down to your process by setting SPID = xxx. Once you configure it, you will feel what is happening.

Answer 2

In fact, you can attach the debugger to your sql server :) - from vs, if you configured it on your sql server.

Check out this link for more information, note that you can set breakpoints :) https://web.archive.org/web/20090303135325/http://dbazine.com/sql/sql-articles/cook1 .

Check out this link for a more general set of information: http://msdn.microsoft.com/en-us/library/zefbf0t6.aspx

Update: Regarding, "There are, however, cases where several stored procedures are involved and finding valid input data is tedious. It is easier to just run things from my web application."

I suggest you set up integration tests focused on specific methods that interact with these procedures. If the procedures are controlled by a web application, this is a great place to conduct valid tests + inputs, which you can run at any time. If there are several applications that interact with procedures, I would look at the unit testing the procedures instead.

Answer 3

I prefer to simply use stored procs to retrieve data and do any complex "work" on the application side. Because you're right, trying to "debug" what happens inside the gut of a multilayer, cursor loop, temp-table using a nested stored procedure is very difficult.

However, MS KB 316549 describes how to use visual studio to debug stored procedures.

In accordance with this article, there are a number of limitations for debugging as follows:

• You cannot break execution.
• You cannot edit and continue.
• You cannot change the order in which instructions are executed.
• Although you can change the value of variables, your changes may not take effect, since the values ​​of the variables are cached.
• The output from the SQL PRINT statement is not displayed.

Change Obviously, if you are the person who runs this stored process, then do not make it a "multi-level, course loop, temp-table using and inested". In my role, DBA, however, is what I meet daily with application developers.

Answer 4

I prefer not to debug, instead I run test development, which almost eliminates the need for debugging.

Answer 5

If you do not know what the actual input will be, you need to test a wide range of inputs, including, in particular, invalid inputs. You must define your test cases before writing your procs. Then you have a reproducible set of tests to run every time someone changes a complex process.

Answer 6

My team uses SP by rule as our database interface; we do this so that the application user can ONLY run SP (with our naming convention).

One of the best practices that we use works well, is that certain test scripts are contained in the SP comments and must be run on each rev SP or on developing a new SP.

You should ALWAYS always test SP as best as possible without using any application layer (for example, through Management Studio).

Answer 7

This trick is quite convenient:

Custom custom profiling events

Answer 8

Make sure you enter the main saved proc in VS2005 / 2008 when it encounters a nested function, press F11 (step in) to enter ... continue debugging ... This was not very obvious from the debug menu.