title: CLR SQL Stored Procedures Testing with Unit Test Project
body: <p>I'm just getting into using VS2008 to write clr stored procedures for SQL 2008.  When writing c# code I am used to having a separate 'Test Project' where I would place all my unit testing code, however it appears at first blush that I can't have the same setup with a clr sql project with stored procedures.  It 'feels' like this can be done, and I'm missing a couple of configuration parameters, but I'm not sure what those might be.</p>
  
  <p>I am using</p>
  
  <ul>
  <li>Visual Studio 2008</li>
  <li>MS Test</li>
  <li>MS SQL 2008</li>
  </ul>
  
  <p>My requirements are:</p>
  
  <ol>
  <li>debug the stored procedure in the visual studio debugger</li>
  <li>have a bunch of unit test to test the stored procedures</li>
  </ol>
  
  <p>Does anyone know how I can have the unit test project properly depoly the stored procedures to the server, connect up to the sql server, and allow me to step through the unit test to the stored procedures that are sitting on the server?</p>
  
  <p>****UPDATE****</p>
  
  <p>Thank you to everyone for the answers so far, however they are not excatly what I'm looking for.</p>
  
  <p><a href="https://stackoverflow.com/questions/1028540/clr-sql-stored-procedures-testing-with-unit-test-project/1057562#1057562">Mark Seemann's</a> answer below is an interesting approach that I did not know about, and I will certainly use when it comes to resetting my database to a known state.  However, I am looking to debug CLR stored procedures, and it doesn't appear Mark's method will allow me to step from my unit test project, through to the SQL server, and debug the code sitting on the server.</p>
  
  <p>I am looking to actually debug the C# sitting on the server, much like the solution <a href="https://stackoverflow.com/questions/1028540/clr-sql-stored-procedures-testing-with-unit-test-project/1068174#1068174">pho3nix</a> listed below.  However, using this standard approach you need to write your test scripts using a 'Test.sql' file and not using a unit test project from within Visual Studio.</p>
  
  <p>I hope I can have two projects in my solution, one for my CLR stored procedures and one for my unit test project.  When I want to run my tests in my unit test project I hope that all of the changes made to my CLR stored procedure project will be published to the server, the test project will start executing, and if I set a break point in the CLR stored procedure when the unit test begins to test that stored procedure it will break on the server and I can then step through the code.</p>
  
  <p>The closest solution I have found so far is by <a href="http://www.simple-talk.com/sql/t-sql-programming/close-those-loopholes---testing-stored-procedures--/" rel="nofollow noreferrer">Alex Kuznetsov and Alex Styler</a>, however using this solution I can not step through to the SQL server.</p>
  
  <p>****UPDATE 2****</p>
  
  <p>This is more of a 'bump' to bring this question back up...I've still had no luck stepping to the SQL server from the unit tests.  Any other thoughts?</p>