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 unable to debug the code. But since last year I started to be cool about it while I found out this “Store Procedure Debugging” thing. I know many developers still like SQL profiler but I always wanted to see the code line by line what happens. Beside profiler has actually different features.
You’ll need Visual Studio 2008 Professional/Team System for enabling this feature. Express or Standard Editions aren’t gonna 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 its kinda look like this:
Right click on a procedure and Open it. For this example I’m gonna 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 is several methods to do that. You can simple hover the mouse on them and change the values or do it in Watch window as you wish. Wanna step into next line try F10/F11 (C#).
Scroll bellow to see some images.