title: SQL Server Unit Testing Stored Procedures - generating testdata
body: <p>I am writing stored procedure unit tests in VS2012 on 2008R2 for a database that keeps a large number of stored procedures, tables and foreign keys.</p>
  
  <p>For each stored procedure test I generate few lines of data in related tables before performing the test.  </p>
  
  <p>I have recognized that this practice will make the tests very sensitive to database changes especially to the additions of not null columns or extra keys. </p>
  
  <p>The cascading impact of such changes may result in having to keep a lot of tests in sync. Some tests may even have nothing to do with the particular change but share one or more related tables therefore will fail on preparation.</p>
  
  <p>Also, a rather inconvenient consequence of this is that it is hard to make disctinction between tests failed on testing conditions and those failed on key violation during preparation. </p>
  
  <p>Thinking on a large scale the working hour consequences may be serious. </p>
  
  <p>Anything I found so far on this topic has been way too general. </p>
  
  <p>Now comes the question: does a relevant best practice exist for the question of test data in dev db vs. generating test data within test?</p>