Debugging is cool. I mean what would have been happened to developers if they couldn’t debug their code!! Speaking of myself at some point, I probably would’ve got pissed off and started to seek some other job. One reason I never was comfortable with database programming was that I was unable to debug the code. But since last year, I started to be cool about it while I found out this “Stored Procedure Debugging” thing. I know many developers still like SQL profiler but I always wanted to see what happens to the code line by line. Besides, the profiler actually has different features.
You’ll need Visual Studio 2008 Professional/Team System for enabling this feature. Express or Standard Editions aren’t going to work. And of course, you’ll need control over your SQL Server.
At first, establish a connection with the database to the project. You do that by choosing View->Server Explorer->Add Connection.
After you establish the connection, it's kinda look like this:
Right click on a procedure and open it. For this example I’m going to debug ‘spGetAcademicQualification’.
Now if you want to debug this proc, right click on the proc and click “Step Into Stored Procedure”.
A window will pop up and show you the parameters and let you put values on them. Then click the OK button to step into the procedure. The debugger will automatically stop execution on the first line of code in the stored procedure. When the debugger has stopped execution, you’ll see a yellow arrow to the left of the line of code just like VS debugger.
If you wonder what else works... I should tell you it seems to me its attitude is actually more likely the VS debugger. You can use break points. Things like Immediate Window, Quick Watch, Add Watch works in here. More importantly, you can view the parameters values. You can also edit them in their scope. There are several methods to do that. You can simply hover the mouse on them and change the values or do it in Watch window as you wish. Want to step into next line, try F10/F11 (C#).
Scroll below to see some images:
