SQLTeam.com | Weblogs | Forums

How to execute result of stored procedure into temp table?

How to execute result of stored procedure into temp table ?

i work on SQL server 2012 I need to get result returned from stored procedure and save it into temp table

the problem is give me error when do that

SELECT *
INTO #TempTable
FROM OPENROWSET('SQLNCLI', 'Server=AHMEDkhalid\khalid;Trusted_Connection=yes;','EXEC sp_ReportDetailsGetALL 3')
GO

I get error

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'exec (@ColumnName)' in procedure 'sp_ReportDetailsGetALL' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
the result returned is dynamic and returned different result content and headers

my procedure as below

create proc  [dbo].[sp_ReportDetailsGetALL] 
@ReportID  nvarchar(20) , 
@ReportDateFrom nvarchar(20) = null, 
@ReportDateTo  nvarchar(20) = null  ,
@SearchString nvarchar(500)= '1=1' 
as


declare  @SortingColumns Nvarchar(200)  = (select SortingColumns from  [dbo].[Reports] where reportid=@ReportID )

if @ReportDateFrom is  null  and  @ReportDateTo is null  
begin 

declare @D  Date = (select Max(ReportDate) from  ReportDetails where  ReportID=@ReportID )


set  @ReportDateFrom =  @D   
set  @ReportDateTo =  @D   


end  




if (select  InRunTime from  [dbo].[Reports] where reportid=@ReportID )  =0
begin


if  (select  IsDownloaded from  [dbo].[Reports] where reportid=@ReportID  ) =0
begin

declare @ColumnName Nvarchar(max) = (SELECT 'select  ' + STUFF((SELECT ',' + 'Text'+CONVERT(varchar(20),ReportHeaderIndex) + ' '+ '['+ReportHeader +']'
            FROM ReportHeaders where ReportID=@ReportID order  by  ReportHeaderIndex 
            FOR XML PATH('')) ,1,1,'') +  ' , convert(nvarchar(20),[ReportDate]) ReportDate  From  ReportDetails R where  ReportDate >= ''' +@ReportDateFrom+'''  and  ReportDate <= '''+ @ReportDateTo  +'''  and  R.ReportID =' +  @ReportID  + '  and  '+@SearchString+'  and IsHistory=0  order by  reportdate desc ' + @SortingColumns AS Txt   )
exec   (@ColumnName) 

end
else  
begin


 select 
   [Conflict Report Name], [# of Parts], [# of Exceptions], [# of Parts Need to Check] , 
ReportDate  , ReportLink 
 from  
 ( select  ROW_NUMBER() OVER(Partition by Text1 ORDER BY ReportDate desc) AS Row_Number_,  Text1 [Conflict Report Name],Text2 [# of Parts],Text3 [# of Exceptions],Text4 [# of Parts Need to Check] , 
convert(nvarchar(20),[ReportDate]) ReportDate  , ReportLink   
From  ReportDetails R where  R.ReportID =9 and ishistory=0) T where  Row_Number_ =1 

order by  reportdate desc  


 
end


end
else
begin
declare @S  nvarchar(200) = (select [ProcedureName]  from  [dbo].[Reports] where reportid=@ReportID  ) 

exec (@S)

end

IF any way to solve that without using openrowset i accept that

I need any thing save my result returned from sp above to temp table ?

Insert into #temp exec sp

Google search

thank you for reply

result of stored procedure is different not fixed so how to create table before

meaning i dont know column returned to make temp table with columns

in the stored procedure itself

at the end

you can do it ... create temp table ...

Are you trying to create a permanent table
and insert data .. ?????

you can also think of Global Temp Tables