Using a temporary table to handle sp_describe_first_result_set

In a previous post (Easing the Testing Stored Procedures with Temporary Tables) I introduced the sp_describe_first_result_set stored procedure. This procedure works like sp_executesql, except that it returns the columns that a query returns rather than the results of the query. But it has a problem. The column name is separated from the datatype and thus I have to do some shaping to be able to use the results in another query.

But why not load a temporary table with the results of sp_describe_first_result_set and shape the results the way I like? I can then take the results, copy it into my test code and away I go. You can tell I got into programming because I was lazy.

Drop  table if exists #ResultStructure
create table #ResultStructure (is_hidden bit NOT NULL
, column_ordinal int NOT NULL
, name sysname NULL
, is_nullable bit NOT NULL
, system_type_id int NOT NULL
, system_type_name nvarchar(256) NULL
, max_length smallint NOT NULL
, precision tinyint NOT NULL
, scale tinyint NOT NULL
, collation_name sysname NULL
, user_type_id int NULL
, user_type_database sysname NULL
, user_type_schema sysname NULL
, user_type_name sysname NULL
, assembly_qualified_type_name nvarchar(4000)
, xml_collection_id int NULL
, xml_collection_database sysname NULL
, xml_collection_schema sysname NULL
, xml_collection_name sysname NULL
, is_xml_document bit NOT NULL
, is_case_sensitive bit NOT NULL
, is_fixed_length_clr_type bit NOT NULL
, source_server sysname NULL
, source_database sysname NULL
, source_schema sysname NULL
, source_table sysname NULL
, source_column sysname NULL
, is_identity_column bit NULL
, is_part_of_unique_key bit NULL
, is_updateable bit NULL
, is_computed_column bit NULL
, is_sparse_column_set bit NULL
, ordinal_in_order_by_list smallint NULL
, order_by_list_length smallint NULL
, order_by_is_descending smallint NULL
, tds_type_id int NOT NULL
, tds_length int NOT NULL
, tds_collation_id int NULL
, tds_collation_sort_id tinyint NULL

Insert #ResultStructure
exec sys.sp_describe_first_result_set N'exec [Integration].[GetOrderUpdates] ''20100101'', ''20170122''';

select case when column_ordinal = 1 then 'create table #out (' else ', ' end
+ QUOTENAME (name) + ' ' + system_type_name
+ case when column_ordinal = max(column_ordinal) over () then ');' else '' end
 from #ResultStructure;


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