Introduction
Debugging is a necessary process in almost any new software development process.
We write complex business logic in store procedure and many companies using store procedure extensively. One of my ERP project have stored procedure which has more then 50 thousand lines and it was very difficult for me to debug them. I had struggled with really challenging bugs in my SQL store procedures. I had been spending many days and sometime weeks just to figure out the exact error for my store procedures.
I am sharing my experience so that you can gain from knowledge and improve your debugging skill for SQL store procedure.
Reader is assumed to have basic knowledge of Visual Studio and SQL store procedures.
Demonstration
To follow this demonstration you need SQL server and Microsoft Visual Studio.
So let’s start step-by-step tutorial for debugging SQL sever 2005 store procedure using Visual Studio IDE.
Add data connection in your Visual Studio 2005.
We first need data connection.
To add a data connection in the Visual Studio 2005 Servers windows right click on "Data Connections" and click on "Add New Connection"
Provide credential for Northwind database
Once we are connected to Northwind database we will have following window
We will debug usp_Region_Insert in our tutorial. You may use different server as well as procedure.
Open the procedure in code window
Debugging Procedure
Right click on usp_Region_Insert store procedure and click "Step Into Stored Procedure"
We will be asked to provide parameters.
I have provided RegionID = 5 and RegionDescription = Southwest
Debugger will execute first line and wait for our instruction.
If you are using VB [F-8] or C# [F-11] is your key to execute next line of code.
We can step by step execute each line of code and debugger will show the value of the variable. We can even modify the value while debugging.
Hovering your mouse on the variable will provide value of it.
We can place breakpoints in a store procedure that is very useful if you are dealing with large store procedure
Conclusion
Debugging is a huge step forward and I am sure it will be very much useful to you.
By now you can debug any store procedure.
I have tried to keep this tutorial as simple as I can, but if you have any queries or comments please feel free to write at umisha.shah@gmail.com.
