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 ?
