Debugging a Stored Procedure

From SQLBWiki

Jump to: navigation, search

SQLBrowser implements a simple step-by-step debugger by merely sending executables statements one by one to the server. The state of local variables is kept, which allows for substitution when sending statements. There are two main usages of the debugger:

  • to understand at what point a stored procedure does not behave the way expected
  • to tune a stored procedure

In both cases, local variables, cursors and temp tables are present in the context, which greately helps debugging compared to doing it by hand.

[edit] Steps to debug a stored procedure

  1. Open the server on which you want to debug a stored procedure
  2. Open a New File and type the invocation of the stored procedure
    Image:SQLBrowserScreenShot084.jpg
  3. Click on Start Debugger
    Image:SQLBrowserScreenShot085.jpg
  4. Click on Step Into to enter the debugged stored procedure body
    Image:SQLBrowserScreenShot086.jpg
  5. Step into / over the executable statements
  6. At any time, you can replay any statement
  7. You can toggle break points by clicking in the line number gutter
    Image:SQLBrowserScreenShot087.jpg
  8. Once you have reached the end of the debugged stored procedure, or if you press Stop before the end, you are presented a summary of all statements IOs. This helps spotting statements with long IOs
    Image:SQLBrowserScreenShot088.jpg
  9. To comletely stop the debugger, press Stop again
    Image:SQLBrowserScreenShot089.jpg
Retrieved from "http://www.sqlbrowser.com/wiki/index.php/Debugging_a_Stored_Procedure"
Personal tools