SQL Server Unit Tests: Possible to create temporary tables in "pre-test" script and use them in the main test?

Question

I am an SQL newbie trying to create unit tests for stored procedures in a legacy database. The stored procedures retrieve input data from temporary tables, which seems like it would make it easy to test them. I would like to create and populate those tables in a test harness setup routine, run the stored procedure, and then evaluate the results.

I set up my test environment using Visual Studio 2013 with a SQL Server Unit Test project as shown in this post.

My Pre-test looks pretty boring:

CREATE TABLE #foo(
/* fields */)

/* Populate temp table here */

Then in the Test body, I call the stored procedure, which depends on table #foo:

DECLARE @RC AS INT;
SELECT @RC = 0;
EXECUTE @RC = [dbo].[MyStoredProcedure] ;
SELECT @RC AS RC;

But when I run the test, I get the following error:

Sql Error: 'Invalid object name '#foo'.' (Severity 16, State 0)

Test method DatabaseTestProj.TestSpike.dbo_SomeStoredPocTest threw exception: 
System.Data.SqlClient.SqlException: Invalid object name '#foo'.

In fact, it seems that I can't even create a variable in Pre-Test and access it from the main part of the test.

What's the "right" way to populate the temporary table? Should the Pre-test area not be used for creating temporary tables?

Answer 1

The issue here is that by default pre and post-test scripts are executed using a different connection (PrivilegedContext) to the test script (ExecutionContext). You are using a local temporary table and the scope of this is only within the current connection. Hence the test cannot access the table since it's not available in its connection scope. There are two possible solutions here:

1. Use a global temp table (##foo instead of #foo). This is available for any connection so you can access it from both Privileged and Execution contexts. The benefit is you don't need to change your test code, the downside is you might not want to change production code just to get your test working

2. Change the connection used to execute either the test action or the pre-test action. This requires editing the C#/VB code that the unit test designer creates. If you right-click on your test class in Solution Explorer and choose "View Code", you can see the contents of the test method. You should be able to change the "preTestResults" line to use the ExecutionContext. See sample below (in this case the test was named "dbo_Procedure1Test").

   [TestMethod()]
   public void dbo_Procedure1Test()
   {
       SqlDatabaseTestActions testActions = this.dbo_Procedure1TestData;
       // Execute the pre-test script
       // 
       System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");
   
       // Original code: uses the "PrivilegedContext" for the connection.
       //SqlExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);
   
       // New code: uses the same "ExecutionContext" for the connection that the test section uses
       SqlExecutionResult[] pretestResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.PretestAction);
   
       try
       {
           // Execute the test script
           // 
           System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");
   
           // The test action uses an ExecutionContext, which is useful if you want to limit the permissions
           // to what an actual caller of the stored procedure would have, but do setup/teardown of test data using
           // a more privileged context. In this case you don't want that, so use the same context for both pre-test and the test itself
           SqlExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction);
       }
       finally
       {
           // Execute the post-test script
           // 
           System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != null), "Executing post-test script...");
           SqlExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction);
       }
   }