Click here to Skip to main content
15,667,577 members
Articles / Web Development / XHTML
Posted 31 Oct 2008


37 bookmarked

SQL Stored Procedure Debugging Using Visual Studio .NET 2005

Rate me:
Please Sign up or sign in to vote.
3.12/5 (12 votes)
31 Oct 2008CPOL2 min read
Demonstrates how to debug a Stored Procedure in Visual Studio .NET 2005.

Introduction & Background

Debugging lengthy Stored Procedures is a very painful process to many programmers. There are many who just guess about the cause and others use some hit and trial methods. Programmers also use the print command to check for problems. But, if a Stored Procedure is calling nested Stored Procedures, then it becomes a huge problem.

Stored Procedures are heavily used in our organization’s applications, and a huge percentage of those have more than 5K lines of code. You can imagine how tough it would be to debug them.

Visual Studio .NET 2005 provides excellent debugger support for SQL programs like Stored Procedures. It’s always a programmer's dream to debug Stored Procedures like any other program in the Visual Studio .NET environment. Now, stepping into Stored Procedures is really very easy, and it definitely help developers find an easy way to debug SQL code.


  • Open the Visual Studio 2005 Integrated Development Environment. Click on the View Server Explorer menu in the toolbox.
  • The Server Explorer will be visible, and it will show all the registered servers in Visual Studio .NET, as shown below:
  • SrvExp.JPG

  • Click on the Connect to Database button, and it will show the Add Connection wizard. Select Microsoft SQL Server as the data source. Also provide the login credentials and select a database. Then, click on the OK button to create a new data connection.
  • ConnWiz.JPG

  • Now, all database objects will be visible if you expand the data connection tab. Then the database object needs to be selected. In this case, I am going to pick a Stored Procedure to debug.
  • Image 3

  • The Step Into Stored Procedure option is available in the menu.
  • Now, you need to provide all the parameter details to provide the execution requirement of the Stored Procedure.
  • Param.JPG

  • Now, it’s your turn to debug the code like any other program in Visual Studio .NET. Step into the various SQL code blocks using F10, F11, and F12 (like VC#.NET) and find the root cause of the problem. I assume that the uses of F10, F11, and F12 in .NET debugging are already known so I am not explaining those terms.
  • Image 5

  • You can use the Quick Watch facility (just like any other program in Visual Studio .NET) to get the value of a variable.
  • Image 6


This is a very basic article to know how to simply debug Stored Procedures in Visual Studio .NET. There are so many programmers who still use SQL Server Management Studio to debug and test their SQL programs. As we saw in the demonstration, it’s a very simple process to debug a Stored Procedure in Visual Studio .NET. It saves a lot of time. What do you think? Please share your valuable feedback regarding this article as it will really help me and other programmers to find better solutions.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
United Kingdom United Kingdom
A SharePoint Kid playing in Kolkata, India.

Working with one of the leading software company and currently working with SharePoint technologies.

Enjoys Cricket, National & World Music.

Favourite band include Linkin Park, Beatles, Oasis, Match Box 20, Noori, Nirvana, Nickelback etc.

Comments and Discussions

SuggestionSql Stored Procedures Debugging from Managed Code or Code behind. (On Page Load, Button Click etc..) Pin
chaitanyatallapalle12-Sep-12 17:43
chaitanyatallapalle12-Sep-12 17:43 
GeneralMy vote of 3 Pin
Amey Deshpande17-Aug-10 0:54
Amey Deshpande17-Aug-10 0:54 
GeneralTalk about Debug window, which shows DB output Pin
PhaniKatakam7-Jul-09 23:59
PhaniKatakam7-Jul-09 23:59 
Also, I did not find success in nested storedprocedure? any Clues?

Phani Katakam

GeneralThanks Pin
panda86931-Oct-08 5:04
panda86931-Oct-08 5:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.