How to debug a stored procedure

The guide describes how to debug a SQL Server stored procedure and illustrates various debugging techniques, such as setting breakpoints and viewing data items.

Prerequisites

For demo purposes, use the scripts to create a test database and populate it with data, create the FillSchedule stored procedure and the GetFirstDayOfMonth function from Sample scripts.

Debug a stored procedure

1. In Database Explorer, right-click the procedure you want to debug and select Step Into, or press F11.

2. In the Edit Parameters window that opens, enter the required parameters.

3. To apply the changes, click OK.

Edit parameters

4. Right-click SET TimeSheetDate = GetFirstDayOfMonth(DateFrom) and select Insert Breakpoint.

Note

The yellow arrow identifies the stack frame where the execution pointer is currently located.

5. To track the TimeSheetDate value while debugging, right-click the TimeSheetDate variable and select Add Watch.

The TimeSheetDate variable is now displayed in the Watches pane.

Tip

To add a variable, select it in the editor and drag it to the Watches pane.

6. To insert a breakpoint for the following code block, right-click any value in the statement and select Insert Breakpoint.

INSERT INTO enumdays (DateOut
    , DayOrder
    , TimeSheetDate)
      VALUES
        @DateOut,
        @RowNumber,
        @TimeSheetDate;

The breakpoints are highlighted in red and displayed in the Breakpoints pane.

Insert another breakpoint

Tip

If the Breakpoints pane is hidden, in the top menu, select Debug > Windows > Breakpoints.

Alternatively, press Ctrl+Alt, B.

7. On the Debug toolbar, click or press F11 until this block is highlighted in yellow.

8. Initialize the TimeSheetDate variable before inserting it into the table.

Initialize a variable

Tip

When the date range spans two months, the cycle first processes all dates from the first month based on the specified day interval, followed by all dates from the second month. Use the Watch pane while debugging to observe how the variable changes with each iteration.

9. Press F11 until SET TimeSheetDate = GetFirstDayOfMonth(DateOut); is highlighted in yellow.

We specified the period for 10 days and thus after the debugging process is over, you will see ten records in the scheduledetail table. To check this, in Database Explorer, right-click the ScheduleDetail table and select Select All Rows.

Select rows from the ScheduleDetail table

10. To stop the debugging, on the Debug toolbar, select .

See the How to Debug a Stored Procedure with dbForge Studio for SQL Server blog article to learn how to debug a stored procedure.