Easing the Testing Stored Procedures with Temporary Tables

I think that we would all agree that we should test our code before we release it.  Sometimes it very easy to do so. For example, testing a view is not difficult.  Say you had to make a small change to the view. You create a new view that returns the same results that the previous did.  Then you can then run various queries to look for differences. Here is one example:

Select * from firstView
Select * from secondView
Select * from secondView
Select * from firstView

Any rows that are returned have some difference. Or you could join on the keys in each view and do brute force comparisons. And if the view takes a long time to run, you could simply SELECT INTO a temporary table.
However, sometimes testing is challenging to do.  How do you compare the results from two stored procedures? You can’t create an EXCEPT query with the results from two stored procedures.

You could insert the results of each stored procedure into temporary tables:

Insert #tempFirst
Exec dbo.usp_myFirstProc
Insert #tempSecond
Exec dbo.usp_mySecondProc
Select * from #tempFirst
Select * from #tempSecond
Select * from #tempSecond
Select * from #tempFirst

But before that, you have to go through all of the tedious work of building the temporary table.  Identifying the columns is easy. Determining the datatypes is tedious.

Since SQL Server 2012, Microsoft has provided a hidden gem sp_describe_first_result_set (
https://msdn.microsoft.com/en-us/library/ff878602.aspx). This procedure takes dynamic SQL like the sp_executesql stored procedure and returns the structure of the first result set that the dynamic SQL returns. It returns column names, datatypes and much more information.

Let’s see how it works. There is a stored procedure in the WideWorldImporters database called GetOrderUpdates. I want to test the results of the procedure. To get the columns and the data types, I run the following query:

exec sys.sp_describe_first_result_set N'exec [Integration].[GetOrderUpdates] ''20100101'', ''20170122''';
Table with columns from sp_describe_first_result_set.

sp_describe_first_result_set Output

I need the name and system_type_name column values to create my temporary table. What I do is copy the results to Excel. Then I use an Excel function to create the column statements for the CREATE TABLE.

 [Order Date Key] date NULL
, [Picked Date Key] date NULL
, [WWI Order ID] int NULL
, [WWI Backorder ID] int NULL
, [Description] nvarchar(100) NULL
, [Package] nvarchar(50) NULL
, [Quantity] int NULL
, [Unit Price] decimal(18,2) NULL
, [Tax Rate] decimal(18,3) NULL
, [Total Excluding Tax] decimal(29,2) NULL
, [Tax Amount] numeric(38,6) NULL
, [Total Including Tax] numeric(38,6) NULL
, [WWI City ID] int NULL
, [WWI Customer ID] int NULL
, [WWI Stock Item ID] int NULL
, [WWI Salesperson ID] int NULL
, [WWI Picker ID] int NULL
, [Last Modified When] datetime2(7) NULL);

insert #Results
exec [Integration].[GetOrderUpdates] '20100101', '20170122'

select * from #Results

After I run this statement I can run various queries. As long as I don’t close the connection, the temporary table persists. I could even load a second table and do a comparison.

This procedure can save a lot of boring work.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in Testing, TSQL. Bookmark the permalink.

Leave a Reply