Skip to content

Instantly share code, notes, and snippets.

@vendettamit
Last active June 13, 2019 17:57
Show Gist options
  • Save vendettamit/246a42f6d4a83524e96d02d5817dba9f to your computer and use it in GitHub Desktop.
Save vendettamit/246a42f6d4a83524e96d02d5817dba9f to your computer and use it in GitHub Desktop.
Get any procedure result in temp table without using OPENROWSET or OPENQUERY
create table #d
(is_hidden bit NULL, column_ordinal int NULL, name sysname NULL, is_nullable bit NULL, system_type_id int NULL, system_type_name nvarchar(256) NULL,
max_length smallint NULL, precision tinyint NULL, scale tinyint 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 NULL,is_case_sensitive bit NULL,is_fixed_length_clr_type bit 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 NULL,tds_length int NULL,tds_collation_id int NULL,
tds_collation_sort_id tinyint NULL)
declare @procname varchar(100) = 'PROCEDURENAME'
declare @param varchar(max) = '''2019-06-06'''
declare @exestr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0
select
@qry = 'Create table ##t(' +
stuff(
(select ',' + name + ' '+ system_type_name + ' NULL'
from #d d For XML Path, TYPE)
.value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')
insert into ##t
Exec '+@procname+' ' + @param+'
'
Exec sp_executesql @qry
select * from ##t
-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment