Recently I had to write quite a few interesting stored procedures for MSSQL server which are covered by unit tests invoked as a part of continuous integration build in Team City.
Setting up the data and parameters for stored procedure takes some time and there are many scenarios thus I started looking for a ways:
- How to debug stored procedures using the existing infrastructure without necessity to extract everything out and use separated debugger in the SQL Server Management Studio.
- How to stub some of the data so the complex parts of queries can be easily verified.
In the end I got working debugging with following setup:
- Stored procedures written in T-SQL for MSSQL.
- Each stored procedure is covered by unit tests written in NUnit.
- Thanks to tip of my colleague MSSQL guru Lubos I was able quickly setup SQL Server snapshots to be able revert the database quickly to its initial state.
- Lubos also proposed very simple way on how to stub some data in procedures.
- In order to be able quickly check what is going on inside the stored procedure use the Visual Studio 2013 debugger including the ability to step into the stored procedure.
Using stubs for data used inside stored procedures
- Motivation here is that it is not always simple enough or even practical setup all the required data directly in the database.
- Downside obviously is that since you are about to alter the stored procedure you have to be very careful.
- My colleague proposed a very simple way for this purpose which seems to work:
- Before running tests take a database snapshot so you can easily revert back.
- Inside procedures use some markers which can be quickly identified and the content between them can be replaced - for example:
[Stub1] SELECT * FROM [MyInvoices] [#Stub1]
- Next before you execute the stored procedure you fetch its source and replace the code between markers with select from data stub (for example temporary table):
[Stub1] SELECT * FROM #MyInvoices [#Stub1]
- Before you exercise the stored procedure you simply populate content of #MyInvoices temporary table and run it.
How to enable T-SQL debugging in Visual Studio 2013
This was the most tricky part of the whole procedure and it may be specific to my setup (MSSQL 2008 R2, VS2013).
- As a prerequisite the Application debugging and SQL/CLR debugging must be enabled for the SQL Server in the SQL Server Object Explorer.
- There are two ways how to get to SQL Server Object Explorer:
- Directly open SQL Server Object Explorer via Visual Studio menu VIEW:
- Alternatively use Server Explorer:
- Firstly add a connection to your database
- Then right click using mouse on registered database and select Browse in SQL Server Object Explorer:
- Once you get into the SQL Server Object Explorer enable both debugging options as visible on picture below:
- Running tests just for verification purposes is very simple and basically any NUnit runner can be used.
- In our case for the standard purpose serves very well Jetbrains Resharper.
- Unfortunately this didn’t work with built-in R# test runner.
- Instead I use NUnit-x86.exe runner (I simply needed to force the process bitness to 32bits but I suppose that NUnit.exe will work as well):
- Load test assembly into NUnit runner.
- Attach Visual Studio 2013 debugger to running process.
- Important part here is to have enabled both - Managed code and T-SQL code debugging prior to attaching to he NUnit-x86.exe process:
- Now set a .breakpoint in .NET code just around the code which is responsible for invocation of the stored procedure you are interested in, for example
- Run unit test from the NUnit runner and have it hit the breakpoint in Visual Studio.
- Now from the _SQL Server Object Explorer open the body of stored procedure (just double-click on it),
- Set a breakpoint inside the procedure.
- And step thru the .NET code which is about to invoke the procedure.
- If everything works well for you you are now inside the stored procedure and you can debug it.
Watching data inside the stored procedure
- You can easily watch content of any variable inside the stored procedure.
- I found very simple trick which can be used to watch also content of temporary tables and table variables.
- At the place you would check the content add following statement (obviously adjusted for correct table/variable name):
DECLARE @v XML = (SELECT * FROM #Parameters FOR XML AUTO, ROOT('MyRoot'))
- Once you will hit the statement in the debugger you can easily watch the content of
@vand visualize it for example via XML Visualizer.