Debugging and Executing Stored Procedures

After you have entered your stored procedure into the source code editor and have saved it, you'll see the new stored procedure name appear in the Data View window under the Stored Procedures icon.

To execute the stored procedure from within Visual InterDev, follow these steps:

  1. Right-click the name of your stored procedure from the list of procedures under the Stored Procedures icon in the Data View window.
  2. Select Execute from the context menu. An Output window will appear showing the results of the stored procedure execution.

Figure 15-6 shows the Output window after execution of the spCompany procedure.

click to view at full size.

Figure 15-6. The Visual InterDev Output window showing the result of executing the spCompany stored procedure.

If you execute a stored procedure that requires input parameters, you'll see a dialog box as shown in Figure 15-7. This Execute dialog box prompts you to enter the values for all parameters that the stored procedure requires. In Figure 15-7, the Execute dialog box is prompting for the City parameter for use by the spCompanybyCity stored procedure. Notice that the Execute dialog box also displays the data type and name of the required parameters.

click to view at full size.

Figure 15-7. The Execute dialog box, in which you enter the value of the required parameters for a stored procedure.

In addition to choosing the Execute option from the context menu, there's another way you can execute stored procedures within Visual InterDev. You can use the SQL pane in the Query Designer. To execute a stored procedure in this manner, carry out the following steps:

  1. Open up the SQL pane within the Query Designer.
  2. Enter your SQL code to execute the stored procedure using the EXEC procedure_name syntax. If you need to pass parameters along to the stored procedure, include them after the procedure name using the following syntax:

     EXEC procedure_name parameter1, parameter2, …parameter n 

  3. Choose Run Query from the Query toolbar to execute the query.

Figure 15-8 shows the output after having executed the spCompanybyCity stored procedure from within the SQL pane and passing it the parameter 'Dallas' for the city name.

If you use the EXEC statement to call a stored procedure from within the SQL pane and you omit some required parameters, you'll get a warning message. For example, if you execute the spCompanybyCity stored procedure without supplying the city name to search for, you'll see a warning message like the one shown in Figure 15-9. You'll also get a similar message if you click the Verify SQL Syntax button on the Query toolbar prior to executing the same command.

Note
If you can't remember the parameters that need to be passed to your stored procedure, it's best to choose the first method of execution using the Execute menu item from the context menu. This way the Execute dialog box will prompt you to fill out all the required parameters. On the other hand, if you know which parameters need to be passed to your stored procedure and you want to be able to more easily read the resulting output from the procedure, use the EXEC statement in the SQL pane. This way you can see the results in the Results Pane, which is easier to read than the Output window.

click to view at full size.

Figure 15-8. Stored procedure output shown in the Results pane of the Query Designer.

click to view at full size.

Figure 15-9. A warning message displayed by Visual InterDev when a stored procedure is missing a required parameter.

Debugging SQL Server Stored Procedures

If you have the Enterprise edition of Microsoft Visual Studio, Visual InterDev includes a SQL debugger that you can use to debug SQL Server stored procedures and triggers in much the same way that you debug other kinds of scripts or programs.

To run the SQL Debugger, you must have the following components installed:

  • Visual Studio, Enterprise Edition
  • SQL Server 6.5 with Service Pack 2
  • Microsoft Windows NT 4.0 or later
  • Workstation running Microsoft Windows 95 or Windows NT 4.0 or later

In addition, you must have installed the SQL Server Debugging components onto your server from the Visual Studio, Enterprise Edition CD-ROM. You must also have set up Distributed COM (DCOM) for SQL Debugging. For more details about the installation and configuration of these components, please see the online Visual Studio documentation.

To debug a stored procedure within Visual InterDev, follow these steps:

  1. In the Data View window, right-click the stored procedure and choose Debug from the context menu. The source code editor window will open with the stored procedure code in it.
  2. Now you can add breakpoints by clicking to the left of the code in the gray vertical border or by right-clicking and choosing Insert Breakpoint from the context menu.

The options on the Debug menu allow you to control the debugging process.

Figure 15-10 shows a stored procedure within the source code editor with some breakpoints applied. You can also see the context menu showing the Insert Breakpoint command.

The Locals window allows you to view the values of variables and parameters. You can also drag an expression from the stored procedure code and drop it into the Watch window. You can open up the Locals window and the Watch window by clicking the appropriate buttons on the Debug toolbar.

click to view at full size.

Figure 15-10. Debugging a stored procedure from within the source code editor.



Programming Microsoft Visual InterDev 6. 0
Programming Microsoft Visual InterDev 6.0
ISBN: 1572318147
EAN: 2147483647
Year: 2005
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net