In my company there is a sophisticate logic layer in Sql Server DB. In order to write tests that will check this code we need to write some T–SQL code. The test methods including a serious of initialization script before and after each one of them. I’m going to share you with a very helpful way to debuging this important code by using SQLCLR abilities.The first thing we should do is to open a new Sql Database Project.
Create a new SQL Server Project, by adding a new project and selecting SQL Server Project Template.
Then we need to add our stored procedure that will run inside the db(using SQL-CLR engine). Lets write a very simple stored procedure (It help us to focus on the global concept).
public static void InsertCustomerStoredProcedure(SqlString name)
using (SqlConnection connection = new SqlConnection("context connection=true"))
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "insert into customers(name) values(@name)";
The next step will be a compliation and deploy to the central db.In order to test this code we should used a sql file from the “Test script” library(One of the solution directories).
I added an executable command there. This command is excute our stored procedure:
As you can see, I added a breakpoint in the stored procedure execution line. When I will run this project in “debug” mode the visual studio will stop here.
Ok, something is missing. When I tried to execute it I got this error:Msg 6263, Level 16, State 1, Line 6
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. This stored procedure will get a customer name as an input and insert it into the customers table.
That's means that we must enable the SQLCLR debug option in our DB if we want to debug code that is being stored inside it.
The “flag” that indicates about this configuration key exist inside the db. You can look at the current Sql CLR flag by execute this command.
In order to enable the SQL CLR debugging just executes this stored procedure:
exec sp_configure 'clr enabled', 1 reconfigure with override
It's time to start debugging our code. Click the “start debuging” button. The visual studio will stop on the fisrt breakpoint. And after we click on the “Step into” button we will enter directly into our stored procedure implementation but this time in debug mode.
When I'll open the Customers table, we will see that the insertion operation that has been executed in the stored procedure was succeeded.
Furthermore, inside a test script file you can also debug existing stored procedures even if they have been created as normal stored procedures directly in the db. You can step into them in debug mode and see all the parameters that you gave them in the watch window.
So, if you need to debug a sophisticated code that was written in stored procedures it’s really recommended to use this debug abilities to track the flow easily.