This section looks at debugging a stored procedure directly from Visual Studio, using either Server Explorer or the Source window. Server Explorer presents the same sort of SQL Server views as Enterprise Manager, but it has the benefit of being integrated into Visual Studio. To debug a stored procedure from Server Explorer, you first need to navigate to that stored procedure. Click the Server node in the Server Explorer window and follow the resulting tree down through the SQL Servers node to the server and database that you require. Finally, click the Stored Procedures node and choose the stored procedure that you wish to debug. Figure 12-2 shows how this looks within Visual Studio.
For the purposes of this demonstration, choose the Northwind database on your favorite development server. This database ships with almost every SQL Server installation, so you should find it available. The stored procedure in this database that you're going to debug is called SalesByCategory . Right-click this stored procedure and choose Step Into Stored Procedure from the context menu. Because the SalesByCategory stored procedure has two parameters, you're now presented with a dialog window asking you to enter these parameters. Because you don't know any good values to enter at this stage, click the Cancel button. Although the stored procedure isn't executed after you click Cancel, the text of the stored procedure is presented in a new tab of the Source window.
You can also see that the Visual Studio IDE has now been given a new menu entitled Database. This menu contains options that allow you to create a new database entity such as a stored procedure or table. But the most interesting menu option is the one entitled Run Selection. The Visual Studio IDE normally allows you to execute only a complete stored procedure, but this option allows you to write an ad hoc script, place the script in any window that displays a stored procedure, select the script by highlighting it, and then execute it. This innocuous menu option is very useful for testing ad hoc scripts or checking part of a stored procedure.
Coming back to the SalesByCategory stored procedure, you can see that the CategoryName parameter will be compared to a column of the same name in the Categories table. So to find a value for this parameter, you need to see the contents of this table. To do this, type the following query just below the stored procedure text:
SELECT * FROM Categories
Highlight the query by selecting it and then choose the Run Selection menu option from the Database menu. The result is a list showing all the rows in the Categories table displayed in the Database Output pane of Visual Studio's Output window.
As a sidebar, there are two other ways to look for suitable values from the Categories table. One method is to right-click the table in Server Explorer and choose the Retrieve Data from Table menu option. This displays every row from the table in a new tab of the Source window. The messier option, which is most useful if you need to create more complex ad hoc SQL queries, is to right-click in the Source window tab displaying the stored procedure and choose Insert SQL from the context menu. This adds yet another Source window tab that shows the Query Builder utility, which is similar to the Query Builder built into Microsoft Access. Close the resulting Add Table dialog window and also close the top two window panes of the Query Builder tab by right-clicking in them and choosing the Hide Pane menu option. These panes allow you to build SQL queries graphically and you don't need them for this simple query. Now you can enter the same query that I mentioned previously and execute the query by clicking the toolbar button marked with an exclamation mark. This displays all the rows in the Categories table in the results pane of the Query Builder tab.
Whichever way you choose to show the contents of the Categories table, now you can pick a value from the CategoryName column to be used as a parameter to the SalesByCategory stored procedure. Select the value Beverages from the query results CategoryName column by double-clicking the value and pressing Ctrl-C (to copy it to the clipboard). Go back to Server Explorer, choose the Step Into Stored Procedure option again, and this time press Ctrl-V to paste the just- copied value as the CategoryName parameter of the stored procedure. Finally, click the OK button, and the debugger will step to the first statement of the stored procedure.
If you hover the mouse over the @OrdYear and @CategoryName variables , you can see that it reports their values. Switch to the Database Output pane of Visual Studio's Output window, and you can see that it reports the name and parameters of the stored procedure being executed. However, if you highlight the expression @OrdYear != ˜1997 on line 4 and do a QuickWatch command, you can see that the debugger is unable to evaluate this SQL expression. Although the debugger can report on the value of most variables, it's not able to evaluate SQL expressions. One of the side effects of this is that conditional expression breakpoints aren't available when debugging SQL. Figure 12-3 shows the stored procedure being debugged .
Now you can step to the next statement in the stored procedure, which selects rows matching the procedure's parameter values from several tables. One important distinction to make here is the difference between a select statement that returns a result set from a stored procedure and a select statement that alters variables local to a stored procedure (i.e., those prefixed with an at [@] symbol). The distinction is that only local variables can be viewed in the debugger, not result sets. A select statement that produces a result set, such as the one in this stored procedure, doesn't return variables. The result set columns that you see are just column names acting as placeholders for the values that will be returned in the result set.
If you step through to the end of the stored procedure, you can view the result set produced by the select statement by looking in the Database Output pane of Visual Studio's Output window.