Introduction
The purpose of this article is to demonstrate how a stored procedure can be debugged from Visual Studio using the same technique as when debugging code. For completeness the article also covers a section on how to debug stored procedures in SQL Server Management Studio (SSMS).
The article is divided into two sections:
Debug Stored Procedure Using Visual Studio
Debug Stored Procedure using SQL Server Management Studio (SSMS)
Background
There are situations when a stored procedure returns an unexpected result or generates an error, whether it is called from an application or from a report. Getting to the root of such errors can be a time consuming task, especially if the logic in the stored procedure is complex. Some legacy systems may be driven by a database where much of the business logic is embedded within stored procedures. In such cases it would be useful to have some means of reverse engineering large stored procedures in order to understand their behavoiur and mechanics from a nuts and bolts perspective.
The aim of this article is to provide a clear example on how stored procedures can be debugged in Visual Studio and SSMS.
The Process
In order demonstrate the process of debugging a stored procedure a sample database is used which contains the proc to be debugged. The attached Script File can be used to create the database and objects which will be used in the article.
Create the database schema
The table relationship is as follows:
- A User can belong to more than one Group
- A Group can have more than one User
- A Group can have more than one Permisson.
- A Permisson can be associated with more than one Group
In simple terms there is a many-to-many relationship between a User and a Group, which is implemented here using a linking table named UserGroup. Similarly, The GroupPermisison table is used to establish a many-to-many relationship between the Group and Permisson tables.
Once the scripts have been executed, the database will contain the following tables.
Debug Stored Procedure Using Visual Studio
In order to debug a stored procedure, the attached Test Project will be used to walk through the steps involved.
The winforms project runs in VS2013, although it should be possible to run it in some earlier versions. It accepts a Start Date and End Date from the UI.
Once the Debug Stored Proc button is clicked, the code in the click event calls spGetActiveUserListByDateCreated, passing in the two date parameters using EF6 Code First approach in this example.
private void btTestDebug_Click(object sender, EventArgs e)
{
using (var context = new UsersContext())
{
var startDate = new SqlParameter("@DateCreatedStart", dtStartDate.Text);
var endDate = new SqlParameter("@DateCreatedEnd", dtEndDate.Text);
var result = context.Database
.SqlQuery<GridResult>("spGetActiveUserListByDateCreated @DateCreatedStart, @DateCreatedEnd", startDate, endDate)
.ToList();
dgResults.DataSource = result;
dgResults.Update();Application Debugging option appears in Server Explorer in VS 2010. In VS 2012 & VS2013 it appears in the SQL Server Object Explorer.
}
}
The code for the POCO classes, DBContext and Mappings in the DataLayer Class Library project was autogenerated using EF Power Tools. More information can be found in the article on Use EF Power Tool to generate POCO classes.
Note that the proc can also be invoked using any other data access method you are familiar with such as using the EF Designer, Enterprise Library, or directly using ADO.NET. Therefore the above code snippet is just an example which will be used for calling a stored procedure in order to invoke the debugger.
A list of Users in the date range provided is returned. The result is subsequently applied to the DataGridView control as shown below.
In order to debug spGetActiveUserListByDateCreated as it is generating this resultset, the following needs to be applied to the solution in Visual Studio.
1. Connect to SQL Server using Sql Server Object Explorer (Server Explorer in VS2010)
2. Double-Click on spGetActiveUserListByDateCreated stored procedure to open in the editor window.
3. Press F9 to set a breakpoint after the declare statements as shown above.
4. Enable the Application Debugging in SQL Server Object Explorer by right-clicking on the Server and selecting the option shown below.
Note that Application Debugging option appears in Server Explorer for VS 2010. In VS 2012 & VS2013 it appears in the SQL Server Object Explorer as shown above.
5. Enable SQL Server Debugging in Project Properties
In the Project Properties window for DebugProcTest, select the Enable SQL Server Debugging checkbox.
Now Build and Run the application.
The execution will halt at the breakpoint in the stored procedure.
On this breakpoint it is possible to step through the T-SQL in the same way as stepping through and debugging C# or VB.NET code in Visual Studio i.e by using the debug toolbar or the debug shortcut keys. The inner working of spGetActiveUserListByDateCreated is debugged in the next section for SSMS.
This section concludes the change which need to be applied to the Visual Studio Project in order to enable the debugging of stored procedures.
Debug Stored Procedure using SQL Server Management Studio (SSMS)
In a New Query window paste in the following T-SQL:
use Users
declare @startdate datetime = '2014.01.01'
declare @enddate datetime = convert( varchar(20), getdate(), 102 )
exec spGetActiveUserListByDateCreated @DateCreatedStart = @startdate ,@DateCreatedEnd = @enddate
This will pass a @startdate and @enddate into spGetActiveUserListByDateCreated and return a list of active users within the date range provided. The above snippet is a sample for calling the test proc and invoking the debugger in SSMS.
The spGetActiveUserListByDateCreatedcan be debugged in SSMS by following these simple steps:
1. Press F9 to set a breakpoint as shown below.
2. Click the Debug button on the toolbar.
The toolbar will change to show additional buttons for stepping through the T-SQL. A yellow highlight on the first line indicates that the window is now in debug mode.
The familiar debugging buttons appear on the toolbar and are similar to those in Visual Studio.
Continue (Alt-F5) - Resume execution up to the next breakpoint
Stop Debugging (Shift-F5)
Step Into (F11) - Step into the inner body of a userdefined object such as a stored procedure or a function
Step Over (F10) - Advance to the next line without stepping into the current line
Step Out (Shift+F11) - Return to the calling routine or exit a loop for example.
3. Press the F10 key to step over to the exec command until it is highlightred in yellow as shown below.
4. Press the F11 key to step into spGetActiveUserListByDateCreated.
The T-SQL for spGetActiveUserListByDateCreated will automatically open in a Query window as shown below.
The locals window indicates the values for the two date parameters which were passed in.
5. Press F10 or the toolbar button to Step Over to the While loop.
Placing the mouse icon over the @id variable displays its value as shown above.
Double-Click on @id until it is highlighted, then press Shift-F9 key. This will display the quick watch window.
6. Press F10 and advance into the While loop.
As mentioned previously, the variables in the While loop can be observed using the locals window, quick watch window or by simply hovering over the variable to check its value. A limitation of SSMS is that it is not possible to watch the contents of the temp table.
The loop fetches firstname, surname, email, and datecreated values from the User table and inserts a row into a temp table. The loop iterates until all rows in the User table have been inserted in the temp table matching the criteria.
If there are many rows to loop through, then it is possible to exit the loop and continue stepping through the code outside the loop by pressing the Shift+F11 key or clicking the step out button .
Once the end of the proc is reached and the last Select clause is executed, control passes to the calling window where the result is returned, as shown below.
This concludes the section on how to debug a stored procedure in SSMS.
Points of Interest
- Application Debugging option appears in
Server Explorer for VS 2010. In VS 2012 & VS2013 it appears in the SQL Server Object Explorer. - It is not possible to watch the contents of a temp table when debugging a stored procedure in SSMS or Visual Studio
- The Debugging cannot be performed on Views.
Conclusion
With the use of a sample project and database, this article demonstrated how Visual Studio and SSMS can be configured to allow the debugging of stored procedures. Debugging in Visual Studio is particularyy handy as it is possible to see the exact parameter values being passed from the code, and to determine if these values are as expected. It is also convenient to be able to step through the logic using the same techniuque as debugging in C# or VB.NET.
Debugging in SSMS is applicable for testing a stored procedure in isolation of the application or reporting tool such as SSRS or Crystal Reports. By passing in test parameter values and stepping through the code, it is possible to determine the correctness of logic, and whether the expected resultset is being returned. Exception handling can also be tested by passing invalid parameters and checking that this is handled correctly.
Being familiar with both techniques should enable the developer to quickly determine the cause of an error or unexpected resultset.
