locked
Pushing Stored Procedure output into #temp table with out using openrowset RRS feed

  • Question

  • Hi,

    Would appreciate if someone can throw light on this issue. I have a stored procedure give by my Client . If i execute the same it returns the result set with a bunch of Columns. But i will never know the number of Columns that will be return as it is Dynamic in Nature.

    The same result set i would want to push it into a #temp Table. Yes i know it can be done in a way by using .. something like  SELECT * INTO MyModelTable   FROM OPENROWSET(.....).

    But the catch is in order to execute the above Select Statement i need to enable the Distributed Transaction in the Sql Server. And i dont think the Client will agree to it .

    So would like to know if there is any work around that this issue could be addressed .

    Regards

    Venkat


    Venkat Chennai

    Wednesday, May 29, 2013 12:10 PM

Answers

  • Hi Venkat,

    If you are using SQL Server 2012, we can use sp_describe_first_result_set system stored procedure to get the metadata of the result set, and then according to the metadata data to count the columns, for example:

    create table DSCResult
    (
    is_hidden bit  NULL,
    column_ordinal int  NULL,
    name sysname,
    is_nullable bit  NULL,
    system_type_id int  NULL,
    system_type_name nvarchar(256),
    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,
    )
    
    
    
    insert into DSCResult (is_hidden,
    column_ordinal,
    name,
    is_nullable,
    system_type_id,
    system_type_name,
    max_length,
    [precision],
    scale,
    collation_name,
    user_type_id,
    user_type_database,
    user_type_schema,
    user_type_name,
    assembly_qualified_type_name,
    xml_collection_id,
    xml_collection_database,
    xml_collection_schema,
    xml_collection_name,
    is_xml_document,
    is_case_sensitive,
    is_fixed_length_clr_type,
    source_server,
    source_database,
    source_schema,
    source_table,
    source_column,
    is_identity_column,
    is_part_of_unique_key,
    is_updateable,
    is_computed_column,
    is_sparse_column_set,
    ordinal_in_order_by_list,
    order_by_list_length,
    order_by_is_descending,
    tds_type_id,
    tds_length,
    tds_collation_id,
    tds_collation_sort_id
    )
    sp_describe_first_result_set N'exec SPName',null,1
    
    select  count(distinct name),source_database,source_schema,source_table from DSCResult 
    group by source_database,source_schema,source_table
    

    If you are using prior version, I think we need to use openrowset commands to get the result. For more detail information, please refer to the following link:

    SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012
    http://blog.sqlauthority.com/2012/03/31/sql-server-sp_describe_first_result_set-new-system-stored-procedure-in-sql-server-2012/



    Allen Li
    TechNet Community Support

    Friday, May 31, 2013 6:59 AM
  • If the result of the stored proc changes every time, you have no choice but to use OPENROWSET.

    This is an extremely poor design.  The schema of the result set from a stored proc should be the same.

    Wednesday, May 29, 2013 1:52 PM
  • Hello,

    You can use SET FMTONLY ON/OFF or you can use the new recommended way to do that: sp_describe_first_result_set/sys.dm_exec_describe_first_result_set.

    And after create dynamically the #temporary table and call again the procedure.

    The disadvantage is that you run twice the proc, and this work only if you have only one result set.

    It should work but it's very ugly

    Regards
    Alex

    Wednesday, May 29, 2013 2:47 PM

All replies

  • Why don't you go for select * into temptable and then output that result. Below is a sample record.


    create table testtable
    (
    id int identity(1,1),
    name varchar(100)
    )
    
    insert into testtable select 'a'
    insert into testtable select 'b'
    
    
    select name into #outputtable from testtable
    
    select * from #outputtable
    
    drop table #outputtable
    
    drop table testtable 





    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, May 29, 2013 12:28 PM
  • I think you ask the wrong question.  Generally, questions of this nature often indicate that you have prematurely chosen an implementation.  Rather than focus on "how", what exactly do you intend to do with the output?  It may be that the best approach to meeting your goal is not based in tsql at all.
    Wednesday, May 29, 2013 12:43 PM
  • try this

    INSERT

    INTO #tmpBus


    Exec

    SpGetRecords'Params'

    Wednesday, May 29, 2013 1:41 PM
  • If the result of the stored proc changes every time, you have no choice but to use OPENROWSET.

    This is an extremely poor design.  The schema of the result set from a stored proc should be the same.

    Wednesday, May 29, 2013 1:52 PM
  • Hello,

    You can use SET FMTONLY ON/OFF or you can use the new recommended way to do that: sp_describe_first_result_set/sys.dm_exec_describe_first_result_set.

    And after create dynamically the #temporary table and call again the procedure.

    The disadvantage is that you run twice the proc, and this work only if you have only one result set.

    It should work but it's very ugly

    Regards
    Alex

    Wednesday, May 29, 2013 2:47 PM
  • That is an idea, I had never thought of.  Thanks Alex.

    Wednesday, May 29, 2013 3:46 PM
  • Hi Alex,

    Thank you for your prompt response.

    But i am sorry to say that i am unable to work on your solution which is slightly unknown to me. The other replies which i have got looks like it is not feasible.

    So would appreciate if you can elaborate on the same a little bit more .

    Regards,

    Venkat


    Venkat Chennai

    Thursday, May 30, 2013 6:14 AM
  • Hello,

    The fmtonly on will not work.

    What I had in mind was:

    declare @sql nvarchar(max)

    -

     

    declare @sql nvarchar(max)

    --start table generation

    select @sql='create table #Test('

    select @sql=isnull(@sql +',','')+name+dbo.GetTypeBySystemType(system_type_id)
        from sys.dm_exec_describe_first_result_set('exec MyProcedure',null,0)

    select @sql=@sql+')'

    --end table generation

    select @sql=@sql+' insert into #Test exec MyProcedure'

    exec (@sql)

    dbo.GetTypeBySystemType it's a function that transform systemtypeId to a string type: example 167 will go to varchar(max) let's say

    Observation: If you have parameters you can replace the second parameter for sys.dm_exec_describe_first_result_set, check the documentation for more info.

    After your post I double checked the documentation and I saw that are some limitations and will not work because of : "If the number of columns differs, an error is thrown and no result is returned." (http://msdn.microsoft.com/en-us/library/ff878602.aspx)

    Alex


    • Edited by Gatej Alexandru Friday, May 31, 2013 6:13 AM Add explanion for dbo.GetTypeBySystemType
    Friday, May 31, 2013 5:56 AM
  • Hello,<o:p></o:p>

    Do you have so many cases? It's not working an if else statement<o:p></o:p>

    If 'condition1 ' 
    begin

    create table #Case1(....)<o:p></o:p>

    insert into #Case1 exec MyProc<o:p></o:p>

    end<o:p></o:p>

    else if <o:p></o:p>

    If 'condition2 ' <o:p></o:p>

    begin<o:p></o:p>

    create table #Case2(....)<o:p></o:p>

    insert into #Case2 exec MyProc<o:p></o:p>

    end <o:p></o:p>

    else if .....


    If either the above solution it's not working and you don't have any other alternative you can try to generate the table string with an CLR. but I don't like this solution either.

    I believe that the things are mix up to much with a solution of this kind. Maybe you can resolve it on pure SQL level.

    This will be the code for CLR. The code is in C#:

    using (SqlConnection connection = new SqlConnection("context connection=true"))
    
            {
    
                SqlCommand sqlCommand = new SqlCommand("MyProc ", connection);
    
                sqlCommand.CommandType = CommandType.StoredProcedure;
    
             try
                {
                    connection.Open();
                    SqlDataAdapter dataAdapt = new SqlDataAdapter();
                    dataAdapt.SelectCommand = sqlCommand;
                    DataTable dataTable = new DataTable();
                    dataAdapt.Fill(dataTable);
      StringBuilder sb=new StringBuilder(“create table #Test(”);
      foreach (DataColumn column in dt.Columns)
      {
          Sb.AppendFormat(“{0} {1}”,column.ColumnName,GetSQLStringTypeByType(column.DataType));
      }
      Sb.Append(“)”);
                    SqlContext.Pipe.SendResultsRow(sb.ToString());
                    SqlContext.Pipe.SendResultsEnd();
                }
             catch (Exception exception)
                {
                     //Log the exception
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
    }

    This will replace the table generation from the previous possible solution.

    If you need more info for CLR please let me know.

    Thanks,
    Alex


    PS: I wrote the code directly in the browser so maybe some syntax error will appear
    Friday, May 31, 2013 6:31 AM
  • Hi Venkat,

    If you are using SQL Server 2012, we can use sp_describe_first_result_set system stored procedure to get the metadata of the result set, and then according to the metadata data to count the columns, for example:

    create table DSCResult
    (
    is_hidden bit  NULL,
    column_ordinal int  NULL,
    name sysname,
    is_nullable bit  NULL,
    system_type_id int  NULL,
    system_type_name nvarchar(256),
    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,
    )
    
    
    
    insert into DSCResult (is_hidden,
    column_ordinal,
    name,
    is_nullable,
    system_type_id,
    system_type_name,
    max_length,
    [precision],
    scale,
    collation_name,
    user_type_id,
    user_type_database,
    user_type_schema,
    user_type_name,
    assembly_qualified_type_name,
    xml_collection_id,
    xml_collection_database,
    xml_collection_schema,
    xml_collection_name,
    is_xml_document,
    is_case_sensitive,
    is_fixed_length_clr_type,
    source_server,
    source_database,
    source_schema,
    source_table,
    source_column,
    is_identity_column,
    is_part_of_unique_key,
    is_updateable,
    is_computed_column,
    is_sparse_column_set,
    ordinal_in_order_by_list,
    order_by_list_length,
    order_by_is_descending,
    tds_type_id,
    tds_length,
    tds_collation_id,
    tds_collation_sort_id
    )
    sp_describe_first_result_set N'exec SPName',null,1
    
    select  count(distinct name),source_database,source_schema,source_table from DSCResult 
    group by source_database,source_schema,source_table
    

    If you are using prior version, I think we need to use openrowset commands to get the result. For more detail information, please refer to the following link:

    SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012
    http://blog.sqlauthority.com/2012/03/31/sql-server-sp_describe_first_result_set-new-system-stored-procedure-in-sql-server-2012/



    Allen Li
    TechNet Community Support

    Friday, May 31, 2013 6:59 AM
  • Venkat

    Try using below xml method....

    declare @tabColumn varchar(max) ,
    @Colname1 varchar(max),
    @Colname varchar(max),
    @sql nvarchar(max) ='',
    @columnname varchar(max),
    @sql_Table nvarchar(max),
    @param NVARCHAR(50),
    @sql1 nvarchar(max),
    @SQL_Xml nvarchar(max),
    @idhoc int,
    @IMPXml xml,
    @count int,
    @Top Int,
    @TopColumn varchar(100)
     
    IF OBJECT_ID('tempdb..#Temp_Insert')Is not null 
    Drop Table #Temp_Insert

    set @SQL_Xml = 'set @IMPXML = ('+@Query+' for xml path(''Header''))'

    exec sp_executesql @SQL_Xml, N'@IMPXML xml output', @IMPXml output

    set @IMPXml = '<Header1>'+Cast(@IMPXml as Nvarchar(max))+'</Header1>'

    If @IMPXml is null
    Begin
    Select ''
    Return
    End

    exec sp_xml_preparedocument @idhoc out, @IMPXml 

    select localname
    into #Top_Column from openxml(@idhoc,'//*',2) 
    where localname not in ('Header','Header1','#text') 
    group by Id,localname 
    order by ID,localname

    Select localname,id = IDENTITY(INT,1,1) into #temp_column from #Top_Column

    select Top 1 @TopColumn=localname from #temp_column

    Select Top 2 @Top=id from #temp_column where localname=@TopColumn

    If @top > 1
    Begin
    select @colname=coalesce(@colname+',','')+localname+' '+'varchar(1000)' 
    from #temp_column 
    where Id < @top

    End
    ELSE
    Begin
    select @colname=coalesce(@colname+',','')+localname+' '+'varchar(1000)' 
    from #temp_column

    End

    Create Table #Temp_Insert(
    Dummy bit 
    )

    select @sql_Table = 'ALTER TABLE #Temp_Insert ADD ' + @colname

    exec(@sql_Table)

    IF OBJECT_ID('tempdb..#Temp_Insert') Is Not Null
    Begin
    Alter Table #Temp_Insert Drop column Dummy
    End

    SELECT @sql = N'
    SELECT *
    FROM OPENXML (@idhoc, ''Header1/Header'', 2)
    WITH ('+@colname+')'

    SELECT @param = N'@idhoc INT' 

    EXEC sp_xml_preparedocument @idhoc OUTPUT, @impxml

    Insert into #Temp_Insert
    EXEC sp_executesql @sql, @param, @idhoc

    EXEC sp_xml_removedocument @idhoc

    Friday, May 31, 2013 10:03 AM