[ Team LiB ]Recipe 9.8 Debugging a SQL Server Stored Procedure
Problem
Given an application that uses a SQL Server stored procedure that is causing errors, you
need to debug the stored procedure.
Solution
Use Visual Studio .NET to debug SQL Server stored procedures (in both standalone
mode and from managed code).
Discussion
Debugging a stored procedure in standalone mode
You can debug a stored procedure in standalone mode from Visual Studio .NET Server
Explorer by following these steps:
1. Open the Server Explorer window in Visual Studio .NET by selecting it from the
View menu.
2. Create a connection to the database or select an existing connection.
3. Select and expand the node for the database that contains the stored procedure.
4. Expand the Stored Procedures node.
5. Right-click on the stored procedure to be debugged and select Step Into Stored
Procedure from the popup menu.
6. If requested, supply the parameter values on the Run Stored Procedure dialog.
Alternatively, if the stored procedure is already open in a source window in Visual Studio
.NET:
1. Right-click on the stored procedure to be debugged and select Step Into Stored
Procedure from the popup menu.
2. If requested, supply the parameter values on the Run Stored Procedure dialog.
Debugging a stored procedure from managed code
To debug a stored procedure from managed code, SQL debugging must be enabled for
the project. Follow these steps:
1. Open the solution.

•
When connection pooling is enabled, debugging a stored procedure called from
native or managed code might not work after the first time. When a connection is
obtained from the pool rather than created, SQL debugging is not reestablished.
•
Changes to locals or parameter variables that are cached by the SQL interpreter
are not automatically modified and there is no way to force the cache to refresh.
SQL Server caches variables when the execution plan determines that they will not
be dynamically loaded for each statement execution or reference.
For more information about debugging SQL stored procedures, see the topic "Debugging
SQL" in the MSDN Library.
[ Team LiB ]