Stored procedure series debug stored procedure SQL Server 2005

debugging directly in the database
debugging directly in the database is the simplest way to debug a stored procedure in SQL Server 2005. In the visual Stuido IDE, you can choose to step into a stored procedure, and then you can step through a single statement, and you can examine and modify T-SQL variables and parameters within the stored procedure. The download package that is available at the end of this article includes a SQL Server 2005 Express version of the Northwind database. I added a stored procedure named "Dothings" that has a parameter of @categoryid. This is a stored procedure that has little practical value, but it has a lot of T-SQL statements and variables, and another parameter, which allows us to better practice debugging stored procedures.

Right-click the dothings stored procedure in Server Explorer and select the option to step through the stored procedure. (Note: SQL Server debugging is supported only by Team systems and professional versions of Visual Studio.) ）

The debugger starts and executes the stored procedure. Because the stored procedure "Dothings" requires an incoming value to @categoryid, a dialog box pops up to let us provide the value. After entering 1, click OK.

The first statement is executed first. You can choose to debug on a per-statement or step-by-step basis (the shortcut keys are F11 and F10 respectively), or you can add parameters and variables to the Watch window. The screenshot as shown in the stored procedure during debugging. The yellow arrow on the left refers to the statement that is currently executing. The Watch window displays the types and values of @categoryid and @avgprice.

After the stored procedure is known, the results are displayed in the Output window.

As you can see, debugging stored procedures directly in the database is very simple and well understood. As simple as you need in Server Explorer, right-click the stored procedure and select Step into the stored procedure.


debugging database objects in an ASP. NET program
Direct debugging in the database allows us to easily debug stored procedures directly in the visual Studio IDE. However, there are times when we need to debug an ASP when it calls the associated stored procedure. This way, when a database object is called, we can debug it.

This debugging method is integrated into the debugging of the application. To use it, you need to do the following steps:
• Add a breakpoint to the database object you want to debug. Database objects are debugged only if they contain breakpoints. For example, in general, when an application calls a stored procedure, you cannot go inside the stored procedure, and if you want to debug the stored procedure, you must set a breakpoint inside the stored procedure.
• Configure the application so that it can debug SQL Server objects. This is easier, you just have to select a check box.
• Disable connection pooling. Connection pooling can improve performance by allowing programs to connect to a database using an idle connection in a connection pool. If you enable it, you will not be able to debug correctly. Because connection pooling is enabled by default, we must disable it when setting the connection string. (Be sure to re-enable connection pooling after you have completed the work of debugging SQL Server objects in an ASP. NET program)

Next, let's take a look at how these steps work.

First, open the "dothings" stored procedure in Visual Studio, and then set a breakpoint on the phrase "DECLARE @AvgPrice money". So the first step is done, and then the second step.

Configure the ASP. NET program so that it supports debugging for SQL Server. Right-click the project and select Properties. A dialog appears as shown in the popup. Select the startup option and then select the SQL Server check box in the debugger section. This completes the first two steps, the final step.

In the final step, we need to modify the connection string so that it disables connection pooling. This task is quite simple, just add a property "Pooling=false" to your connection string. Suppose you define the connection string information in the <connectionString> node of Web. config, then the modified connection string looks like this:

<connectionStrings> <add name= "NorthwindConnectionString" connectionstring= "Data source=./sqlexpress; attachdbfilename=| Datadirectory|/northwnd. Mdf;integrated security=true; User instance=true; Pooling=false "providername=" System.Data.SqlClient "/> </connectionStrings>

Now, we have completed these 3 steps. To demonstrate how to debug, let's create an ASP. NET page that calls the "dothings" stored procedure. This page is included in the demo you downloaded at the end of this article. When you debug an ASP. NET program and access this page, the program pauses the breakpoint in the stored procedure. You can then step through the stored procedures, and you can view and modify the values of parameters and variables in the Watch window just as you would debug a stored procedure directly in a database.

Conclusion
A separate tool for debugging stored procedures is available in the previous versions of SQL Server. Now SQL Server 2005, stored procedures, triggers, and user-defined functions (UDFs) are debugged through visual Studio Team system and professional. It brings us 3 ways to debug SQL Server: Direct debugging in the database, application debugging, and debugging in a SQL Server project. In this article, we worked together on the first two debugging methods (using a local database).

Stored procedure series debug stored procedure SQL Server 2005