Tony’s Weblog

ASP.NET, MVC, AJAX, SilverLight, C#, WCF Web Service, SQL 2005 and other interesting stuff.

Archive for the ‘Debugging’ Category

How to Debug Stored Procedure from VS 2008?

Posted by Tony on August 1, 2008

Developers are an inherently finicky group. Most have their favorite tools to complete everyday tasks. This includes text editors as well as IDE’s and even operating systems. While I have favorite tools as well, I am surprised at how little developers know about some of the tools they use everyday. One tool can often replace the functionality provided by numerous smaller programs. A good example is VS.NET (Visual Studio .NET). It provides the ability to develop .NET-based code via languages like C# and VB.NET, but it also facilitates the development of CSS (Cascaded Style Sheets), HTML, XML, and SQL. SQL seems to be the most often overlooked feature, so let’s take a closer look at using VS.NET to work with SQL.

Database connectivity
VS.NET’s Server Explorer feature makes it easy to connect to a database server. While this connection is not restricted to SQL Server, it will be the focus of this article. Figure A shows the Server Explorer on the left side of the VS.NET workspace. It is accessible from the View drop-down menu or the tab on the left edge of the workspace. Right-clicking on the Data Connections node in the Server Explorer provides the option to add a new connection. Figure B shows the dialog presented when this option is selected. The Test Connection button in Figure B allows the connection to be easily tested and verified to be correct.

Figure A
Visual Studio .NET Server Explorer
Figure B
Adding a new server via Server Explorer

Once a connection has been added via Server Explorer, it is available within the Servers node in Server Explorer. The server name appears with its options available under it. The SQL Servers node under the server provides access to the SQL Server and its elements. You are now one step away from debugging SQL code, but additional setup is necessary.

SQL Server setup
SQL Server contains an extended stored procedure that is used to control access to debugging its stored procedures. This stored procedure is named sp_sdidebug, and the following T-SQL may be used to allow a user to debug stored procedures:
USE Master
GRANT EXECUTE
ON sp_sdidebug
TO username

In addition, the user must have rights to the both the database and stored procedure. Once the user has been properly set up, the actual debug process is as simple as right-clicking on the stored procedure within VS.NET’s Server Explorer and selecting Step Into Stored Procedure. Figure C shows this option with an example stored procedure from the Northwind database highlighted. The stored procedure returns the top ten most expensive products from the database.

Figure C
Debugging a stored procedure via Server Explorer

Once the debugging process begins, it is no different than debugging C# or VB.NET code. Figure D shows the previous stored procedure running in debug mode. This option is available via the Debug drop-down menu or with the keyboard shortcut. Here is an overview of the debug-related keyboard shortcuts:

  • F5: Continue
  • Shift + F5: Stop debugging
  • F11: Step into
  • F10: Step over
  • Shift + F11: Step out
Figure D
Stored procedure in debug mode

In addition, breakpoints and watches may be set to monitor the stored procedure in action. The output of the process is available via the lower window within VS.NET (once it has finished running). Figure E shows the VS.NET workspace after running a stored procedure with debug information visible. It says the stored procedure ran and finished with an exit code of zero, which means success.

Figure E
Debug information after running stored procedure

The output may be viewed by selecting Database Output from the drop-down list above the debug window as shown in Figure F. The actual output is visible in Figure F as well.

Figure F
Viewing stored procedure output

Parameters
The stored procedure used up to this point is very simple but not appropriate in the real world. Most real-world stored procedures accept and possibly return parameters. Utilizing these procedures within the debugger is simple; it allows you to add parameter values to see how the code may perform. When a stored procedure that accepts input parameters is debugged, a window is presented to accept the values.

Figure G shows the dialog box that accepts the input parameters. I selected a stored procedure (Sales by Year) that accepts a beginning and ending date value to return sales data. In addition to the dates you may enter, NULL and DEFAULT options may be selected. This allows you to test error handling (with NULL values) and the default settings.

Figure G
Debugging with parameters

A central location
Using VS.NET to develop, maintain, and debug stored procedures makes other programs such as SQL Server’s Query Analyzer unnecessary. This leads to a more focused developer that doesn’t have to switch applications and mindsets during their day. Debugging is a critical aspect of development, so applying a strong developing tool to T-SQL code makes it easier to build robust applications

Posted in Debugging | 1 Comment »