What's your favored method for debugging MS SQL stored procedures?

Question

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

There are however cases where more than one stored procedure is involved and finding valid data to input is tedious. It's easier to just trigger things from within my web app.

I have a little experience with the profiler but I haven't found a way to explore what's going on line by line in my stored procedures.

What are your methods?

Thank you, as always.

Note: I'm assuming use of SQL Server 2005+

Answer 1

You can actually attach a debugger to your sql server :) - from vs, given you have configured that on your sql server.

Check this link for more info, notice you can set break points :) http://www.dbazine.com/sql/sql-articles/cook1.

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

Update: Regarding "There are however cases where more than one stored procedure is involved and finding valid data to input is tedious. It's easier to just trigger things from within my web app."

I suggest you set up integration tests, focused on the specific methods that interact with those procedures. If the procedures are being driven by the web app, it is a great place to have valid tests + inputs you can run at any time. If there are multiple apps that interact with the procedures, I would look at unit testing the procedures instead.

Answer 2

I prefer to just use stored procs for dataset retrieval, and do any complex "work" on the application side. Because you are correct, trying to "debug" what's happening inside the guts of a many layered, cursor-looping, temp-table using, nested stored proc is very difficult.

That said, MS KB 316549 describes how to use visual studio to debug stored procs.

According to this article, there are a number of limitations to debugging in this fashion:

• You cannot "break" execution.
• You cannot "edit and continue."
• You cannot change the order of statement execution.
• Although you can change the value of variables, your changes may not take effect because the variable values are cached.
• Output from the SQL PRINT statement is not displayed.

Edit: Obviously, if you are the person making this stored proc, then don't make it "many layered, cursor-looping, temp-table using, and nested". In my role as a DBA, though, that's pretty much what I encounter daily from the app developers.

Answer 3

Profiler is very handy, just add SP:StmtStarting events, and filter the activity down to just your process by setting SPID=xxx. Once you have it set up, it's a breeze to see what's going on.

Answer 4

As far as not knowing what the valid inputs would be, you need to test a wide range of inputs including especially invalid inputs. You should define your test cases before you write your procs. Then you have a reproducable set of tests to run every time someone changes the complex process.

Answer 5

My team uses SPs by rule as our interface to the database; we do it in a way that the application user can ONLY execute SPs (with our naming convention).

One best practice that we use, that works well, is that certain test scripts are contained within the SP comments, and must be executed on each rev of an SP, or development of a new SP.

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

Answer 6

This trick is pretty handy:

Custom user configurable Profiler Events

Answer 7

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

Answer 8

I prefer not to debug, I do test driven development instead, which almost eliminates the need to debug.