Skip to Content
Previous

Debugging Stored Procedures

By Craig Cmehil

Leveraging SQLScript in Stored Procedures & User Defined Functions

You will learn

In the following exercise we will show how to debug a procedure using the SQLScript debugger. This includes setting breakpoints, evaluating expressions and intermediate results.
Please note - This tutorial is based on SPS11

Details


Step 1: Start the debugger

Go to the HRTT page and open the procedure called dev602.procedures::get_po_header_data.

HRTT

From the menu, click SQL Debugger, then Debug Settings.

SQL debugger

For the service name, click the drop down box.

service name

Choose your container associated with your user id. Click “OK”.

container

Click the “Connect” button.

connect

Click “Apply”, then “Close”.

apply
Please log in to access this content.
Step 2: Set breakpoints

Set breakpoints at the lines shown here by simply clicking on the line number.

breakpoints

Click Invoke Procedure.

invoke

A new SQL tab will be opened. Click on the Run button.

SQL tab
Please log in to access this content.
Step 3: View the debugger pane

Execution of the procedure has started, and a debugger pane is now visible which is displayed on the right. You can see all of the variables and parameters for this procedure. You might notice that this pane is currently not showing the intermediate table variables at this point.

execute procedure
Please log in to access this content.
Step 4: Use step over

Click the Step Over button.

step over

You will notice that execution has continued to the next statement. In the debugger pane, a new local variable has been added. This is because it is not defined explicitly will be implicitly declared at runtime during first usage.

next step

To see the data for this intermediate table variable, right click on it and choose “Display Content”.

display content

A new window is then opened showing the data in the table. Review the data and close the window by clicking the “X” in the upper right hand corner.

review data
Please log in to access this content.
Step 5: View expression editor

Click the Toggle Expression Editor button.

toggle expression editor

In the yellow box that appears below, enter a SELECT statement as shown here and hit Enter.

select statement

You will notice the expression is then added to the “Expressions” section above.

expressions
Please log in to access this content.
Step 6: View expressions

Right click on the expression and choose Display Content.

display content

Review the results and close the window by clicking the “X”.

review results
Please log in to access this content.
Step 7: Check output

Once again click the Step Over button. Notice the next intermediate table variables is also added. You can review the data in this table as well.

step over

Continue to step through the code and when execution stops at the END statement of the procedure, display the contents of the output parameter the same way you did for the intermediate table variables. Finally, close the window by clicking the “X”.

continue steps
Please log in to access this content.
Step 8: Resume execution

Click the Resume button.

resume

Execution of the procedure is now completed. Return to the SQL tab and check the results.

results
Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

10 Min.

Intermediate

Prerequisites

Next Steps

Next
Back to top