0

I want to Execute one procedure from another procedure and store specific columns from the result of procedure in a temp table without declaring it but Columns are not known and changes depends on the conditions.

I have tried following code but its not working 

select * into #Temp1 from Exec(Procedure @parameter)

Is there any way to do this?

user2148124
  • 940
  • 1
  • 7
  • 20
  • Can you explain what you mean by `store specific columns from the result` and `but Columns are not known and changes depends on the conditions`. Be a bit more clearer. – SouravA Mar 12 '15 at 10:46
  • Procedure returns Column A, Column B & Column C but i want only Column A or sometimes Column A & Column B in return. – user2148124 Mar 12 '15 at 10:53
  • I guess this can solve your problem http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table – krishna Ram Mar 12 '15 at 11:12

1 Answers1

1

Use OPENROWSET to insert the SP's result into temp table

Before using OPENROWSET you have to configure Ad Hoc Distributed Queries

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #Temp 
FROM OPENROWSET('SQLNCLI', 'Server=yourservername;Trusted_Connection=yes;',
     'EXEC Procedure @parameter')

SELECT * FROM #Temp
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • i am unable to use OPENROWSET because of security issues. Is there any other way? – user2148124 Mar 12 '15 at 10:51
  • Yes. I'm getting following error. SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online. – user2148124 Mar 12 '15 at 11:02
  • @user2148124 Okay contact your DBA. Without knowing the number of returned by SP this is the only way to store the result into temp table. – Pரதீப் Mar 12 '15 at 11:03