They come particularly in handy when you don’t want to debug all the lines of code, rather you want to check the status of variables at specific locations in your code. Breakpoint lets you specify the location where you want your debugger to stop executing the code. Note: cls command clears the immediate window! BreakpointsĪ breakpoint is one of the most commonly used debugging tools in any programming language. The following screenshot contains an example of an immediate window. For example, you can perform mathematical operations within the Immediate window, check for the values of variables, etc. The Immediate window is similar to the console window. The second line indicates that this stored procedure was called from the script at Line 7 of the SQLQuery2.sql file. The first line of the Call Stack in the figure above, tells us that currently the spShowOddNumbers stored procedure is being executed and at the moment the debugger is at line 14 of the file that contains this stored procedure. The Call Stack, as the name suggests, displays the stack of calls that have been executed up to the current point. Similarly, to remove a variable, right-click the name of the variable in the Watch window and click “Delete Watch” from the list of options. To add a variable to the Watch window, simply select the variable, right click and then select “Add Watch” as shown in the following screenshot: The only difference is that you can add to or remove variables from the Watch window, which can be useful when working with large numbers of variables in larger scripts. The Watch window is very similar to the Locals window.
If you close the Locals window accidentally and you want to bring it back, you can simply go to Debug -> Windows -> Locals as shown below: Using the Locals window makes it very simple to can keep track of the changes in the values of your variables as you progress through the script.
The Locals window displays the Name, Value, and Type of the variable. Once you execute the loop and then check the value of the variables you will see that the and variables will both show 20 as their value. If the line of script being executed doesn’t contain any call to the stored procedure in SQL Server. It is important to mention that Step Into works like Step Over.
Step Into lets you break into the stored procedure script from the script that calls that stored procedure. This is one of the most important and useful debugging features. For instance, if the debugger is at the following location in the script:Ĭlicking Step Over or pressing F10 simply moves the cursor to the following line: Step Over simply moves the cursor to the next line of executable script. You can see these options by clicking Debug as shown in the screenshot below: There are three options to step through the code: 1) Step Over, 2) Step Into and 3) Step Out. Now you have started debugging you can step through the code. To start debugging a SQL server stored procedure in SQL Server, press ALT + F5, or go to Debug -> Start Debugging, as shown in the figure below:Īfter starting the debugger in the window where you are calling your stored procedure in SQL Server, you will see that there will be a yellow cursor at the start of the query window as shown below: In SQL Server Management Studio, you have a variety of debugging options.