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;