Menu
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+
792
To debug a function, open the procedure calling that function and insert a breakpoint for the function you want to debug. Then, start debugging. Step through the code using the F11 key or Step Into, or press CTRL+F5 to move directly to the breakpoint. Press F11 or click Step Into to get inside the stored function.
To debugging SP, go to database->Programmability->Stored Procedures-> right click the procedure you want to debug->select Debug Procedure.
In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.
SQL Developer's default “debug” action is to run until a breakpoint occurs. You can change this by going to Tools > Preferences, and clicking Debugger. Change the option that says “Start Debugging Option” to Step Into. This will allow you to click Debug and run to the first line of code.
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.
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
