How to: Use Breakpoints With Stored Procedures, User-Defined Functions, and Triggers
You can debug Microsoft SQL Server stored procedures, user-defined functions, and triggers using breakpoints in much the same way that you debug other kinds of scripts or program source code.
When you are debugging an SQL routine, the breakpoints are actually stored within the solution file. If you want to close and then re-open the integrated development environment with the same breakpoints, you must re-open the same solution that was open when you previously debugged and saved the SQL routine.
Following are the most common procedures for using breakpoints with Microsoft SQL Server.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
To open a stored procedure, user-defined function, or trigger
Make a connection to a database.
Right-click the stored procedure, user-defined function, or trigger you want to work with and choose Open from the shortcut menu.
This opens the SQL routine in the editor window.
To insert a breakpoint
Right-click the line of SQL syntax in the editor window where you want to insert a breakpoint.
Point to Breakpoint and then click Insert Breakpoint.
You can modify which breakpoints are active by either removing or disabling existing breakpoints. You can also clear all breakpoints and modify breakpoint properties.
To remove a breakpoint
Position the cursor on the line of SQL syntax that contains the breakpoint.
Right-click, point to Breakpoint, and then click Remove Breakpoint.
To disable a breakpoint
Position the cursor on the line of SQL syntax that contains the breakpoint.
Right-click, point to Breakpoint, and then click Disable Breakpoint.
To delete or disable all breakpoints
Position the cursor on the line of SQL syntax that contains the breakpoint.
From the Debug menu, click Delete All Breakpoints or Disable All Breakpoints.
To change breakpoint properties
Position the cursor on the line of SQL syntax that contains the breakpoint.
Right-click, point to Breakpoint, and then click the property you want to modify. The options are Location, Condition, Hit Count, Filter, and When Hit.