Click here to Skip to main content
14,640,723 members

How to execute result of stored procedure into temp table or variable?

ahmed_sa asked:

Open original thread
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 OR Variable or any thing .

the problem is give me error when do that

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

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' 

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

if @ReportDateFrom is  null  and  @ReportDateTo is null  

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

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


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

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

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) 


   [Conflict Report Name], [# of Parts], [# of Exceptions], [# of Parts Need to Check] , 
ReportDate  , ReportLink 
 ( 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  


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

exec (@S)


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

I need any thing save my result returned from stored procedure above to temp table or variable or any thing ?

What I have tried:

INTO #TempTable
FROM OPENROWSET('SQLNCLI', 'Server=AHMED-SALAH-PC\AHMEDSA;Trusted_Connection=yes;','EXEC sp_ReportDetailsGetALL 3')

EXEC('exec DashBoardDB.[dbo].sp_ReportDetailsGetALL 3 WITH RESULT SETS (( ReportID int));')
Tags: SQL, stored-procedure


When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100