Inserire i risultati di una stored procedure in una tabella temporanea

Come si fa a SELECT * INTO [temp table] FROM [stored procedure] ? Non da FROM [Table] e senza definire [temp table] ?

Select tutti i dati da BusinessLine in tmpBusLine funziona tmpBusLine .

 select * into tmpBusLine from BusinessLine 

Sto provando lo stesso, ma usando una stored procedure che restituisce i dati, non è esattamente la stessa cosa.

 select * into tmpBusLine from exec getBusinessLineHistory '16 Mar 2009' 

Messaggio di uscita:

Messaggio 156, livello 15, stato 1, riga 2 Sintassi non corretta accanto alla parola chiave “exec”.

Ho letto diversi esempi di creazione di una tabella temporanea con la stessa struttura della stored procedure di output, che funziona bene, ma sarebbe bello non fornire alcuna colonna.

Puoi usare OPENROWSET per questo. Dare un’occhiata. Ho anche incluso il codice sp_configure per abilitare le query distribuite ad hoc, nel caso in cui non sia già abilitato.

 CREATE PROC getBusinessLineHistory AS BEGIN SELECT * FROM sys.databases END GO sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC getBusinessLineHistory') SELECT * FROM #MyTempTable 

Se si desidera farlo senza prima dichiarare la tabella temporanea, è ansible provare a creare una funzione definita dall’utente anziché una stored procedure e rendere tale funzione definita dall’utente restituire una tabella. In alternativa, se si desidera utilizzare la procedura memorizzata, provare qualcosa del genere:

 CREATE TABLE #tmpBus ( COL1 INT, COL2 INT ) INSERT INTO #tmpBus Exec SpGetRecords 'Params' 

In SQL Server 2005 è ansible utilizzare INSERT INTO ... EXEC per inserire il risultato di una stored procedure in una tabella. Dalla documentazione INSERT di MSDN (per SQL Server 2000, infatti):

 --INSERT...EXECUTE procedure example INSERT author_sales EXECUTE get_author_sales 

Questa è una risposta a una versione leggermente modificata della tua domanda. Se è ansible abbandonare l’utilizzo di una stored procedure per una funzione definita dall’utente, è ansible utilizzare una funzione definita dall’utente con valore inline. Questa è essenzialmente una stored procedure (prenderà parametri) che restituisce una tabella come set di risultati; e quindi si posizionerà bene con una dichiarazione INTO.

Ecco un buon articolo veloce su di esso e altre funzioni definite dall’utente. Se si ha ancora bisogno di una procedura memorizzata, è ansible racchiudere la funzione definita dall’utente con valori in tabella con una stored procedure. La stored procedure passa semplicemente i parametri quando chiama select * dalla funzione definita dall’utente con valori inline.

Quindi, ad esempio, avresti una funzione definita dall’utente con valore inline per ottenere un elenco di clienti per una particolare regione:

 CREATE FUNCTION CustomersByRegion ( @RegionID int ) RETURNS TABLE AS RETURN SELECT * FROM customers WHERE RegionID = @RegionID GO 

È quindi ansible chiamare questa funzione per ottenere ciò che i risultati sono tali:

 SELECT * FROM CustomersbyRegion(1) 

O per fare un SELECT INTO:

 SELECT * INTO CustList FROM CustomersbyRegion(1) 

Se hai ancora bisogno di una procedura memorizzata, quindi avvolgere la funzione in quanto tale:

 CREATE PROCEDURE uspCustomersByRegion ( @regionID int ) AS BEGIN SELECT * FROM CustomersbyRegion(@regionID); END GO 

Penso che questo sia il metodo più “senza compromessi” per ottenere i risultati desiderati. Utilizza le funzionalità esistenti in quanto erano destinate ad essere utilizzate senza ulteriori complicazioni. Annidando la funzione definita dall’utente con valori inline nella stored procedure, è ansible accedere alla funzionalità in due modi. Più! Hai solo un punto di manutenzione per il codice SQL reale.

È stato suggerito l’utilizzo di OPENROWSET, ma non è ciò per cui è stata concepita la funzione OPENROWSET (From Books Online):

Include tutte le informazioni di connessione necessarie per accedere ai dati remoti da un’origine dati OLE DB. Questo metodo è un’alternativa all’accesso alle tabelle in un server collegato ed è un metodo ad hoc unico per connettere e accedere ai dati remoti utilizzando OLE DB. Per riferimenti più frequenti alle origini dati OLE DB, utilizzare invece i server collegati.

L’utilizzo di OPENROWSET porterà a termine il lavoro, ma ciò comporterà un sovraccarico aggiuntivo per l’apertura delle connessioni locali e dei dati di marshalling. Inoltre, potrebbe non essere un’opzione in tutti i casi poiché richiede un permesso di query ad hoc che pone un rischio per la sicurezza e pertanto potrebbe non essere desiderato. Inoltre, l’approccio OPENROWSET preclude l’uso di stored procedure che restituiscono più di un set di risultati. Racchiudere più funzioni definite dall’utente in valori di tabella in linea in una singola stored procedure può ottenere ciò.

 SELECT * INTO #tmpTable FROM OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1') 

La soluzione più semplice:

 CREATE TABLE #temp (...); INSERT INTO #temp EXEC [sproc]; 

Se non conosci lo schema, puoi fare quanto segue. Si prega di notare che ci sono gravi rischi per la sicurezza in questo metodo.

 SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC [db].[schema].[sproc]') 

Quando la stored procedure restituisce molte colonne e non si vuole “creare” manualmente una tabella temporanea per contenere il risultato, ho trovato che il modo più semplice è entrare nella stored procedure e aggiungere una clausola “in” sul ultima istruzione select e aggiungi 1 = 0 alla clausola where.

Esegui la stored procedure una volta e torna indietro e rimuovi il codice SQL appena aggiunto. Ora, avrai una tabella vuota che corrisponde al risultato della procedura memorizzata. Puoi “creare una tabella come creato” per una tabella temporanea o semplicemente inserirla direttamente in quella tabella.

 declare @temp table ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); INSERT @temp Exec sp_helpfile; select * from @temp; 

La tua stored procedure recupera solo i dati o li modifica? Se viene utilizzato solo per il recupero, è ansible convertire la stored procedure in una funzione e utilizzare le Common Table Expressions (CTE) senza doverla dichiarare, come segue:

 with temp as ( select * from dbo.fnFunctionName(10, 20) ) select col1, col2 from temp 

Tuttavia, qualunque cosa debba essere recuperata dal CTE dovrebbe essere usata in una sola istruzione. Non si può fare a with temp as ... e provare a usarlo dopo un paio di righe di SQL. È ansible avere più CTE in un’unica istruzione per query più complesse.

Per esempio,

 with temp1020 as ( select id from dbo.fnFunctionName(10, 20) ), temp2030 as ( select id from dbo.fnFunctionName(20, 30) ) select * from temp1020 where id not in (select id from temp2030) 

Se la tabella dei risultati del processo memorizzato è troppo complicata per scrivere manualmente la dichiarazione “Crea tabella” e non è ansible utilizzare OPENQUERY O OPENROWSET, è ansible utilizzare sp_help per generare l’elenco di colonne e tipi di dati per l’utente. Una volta ottenuto l’elenco delle colonne, è solo questione di formattarlo in base alle proprie esigenze.

Passaggio 1: aggiungere “in #temp” alla query di output (ad esempio “selezionare […] in #temp da […]”).

Il modo più semplice è modificare direttamente la query di output nel proc. se non è ansible modificare il proc memorizzato, è ansible copiare il contenuto in una nuova finestra di query e modificare la query lì.

Passaggio 2: eseguire sp_help sulla tabella temporanea. (ad esempio “exec tempdb..sp_help #temp”)

Dopo aver creato la tabella temporanea, eseguire sp_help sulla tabella temporanea per ottenere un elenco di colonne e tipi di dati, inclusa la dimensione dei campi varchar.

Passaggio 3: copia le colonne e i tipi di dati in un’istruzione table create

Ho un foglio di Excel che uso per formattare l’output di sp_help in un’istruzione “create table”. Non hai bisogno di nulla di interessante, basta copiare e incollare nel tuo editor SQL. Usa i nomi delle colonne, le dimensioni e i tipi per build una dichiarazione “Crea tabella #x […]” o “dichiarazione @x tabella […]” che puoi usare per INSERIRE i risultati della stored procedure.

Passaggio 4: inserire nella tabella appena creata

Ora avrai una query simile alle altre soluzioni descritte in questo thread.

 DECLARE @t TABLE ( --these columns were copied from sp_help COL1 INT, COL2 INT ) INSERT INTO @t Exec spMyProc 

Questa tecnica può anche essere utilizzata per convertire una tabella temporanea ( #temp ) in una variabile di tabella ( @temp ). Anche se questo può essere più di un semplice processo di create table dell’istruzione create table , impedisce errori manuali come errori di battitura e mancanze di tipo di dati nei processi di grandi dimensioni. Il debug di un refuso può richiedere più tempo rispetto alla scrittura della query.

Quassnoi mi ha messo quasi tutto lì, ma mancava una cosa:

**** Ho avuto bisogno di utilizzare i parametri nella stored procedure. ****

E OPENQUERY non consente che ciò avvenga:

Così ho trovato un modo per lavorare il sistema e anche non dover rendere la definizione della tabella così rigida, e ridefinirla in un’altra stored procedure (e ovviamente rischiare che si possa rompere)!

Sì, è ansible creare dynamicmente la definizione della tabella restituita dalla stored procedure utilizzando l’istruzione OPENQUERY con bogus varaiables (a condizione che NO RESULT SET restituisca lo stesso numero di campi e nella stessa posizione di un set di dati con dati validi).

Una volta creata la tabella, è ansible utilizzare la stored procedure exec nella tabella temporanea per tutto il giorno.


E per notare (come indicato sopra) è necessario abilitare l’accesso ai dati,

 EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE 

Codice:

 declare @locCompanyId varchar(8) declare @locDateOne datetime declare @locDateTwo datetime set @locDateOne = '2/11/2010' set @locDateTwo = getdate() --Build temporary table (based on bogus variable values) --because we just want the table definition and --since openquery does not allow variable definitions... --I am going to use bogus variables to get the table defintion. select * into #tempCoAttendanceRpt20100211 FROM OPENQUERY(DBASESERVER, 'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"') set @locCompanyId = '7753231' insert into #tempCoAttendanceRpt20100211 EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo set @locCompanyId = '9872231' insert into #tempCoAttendanceRpt20100211 EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo select * from #tempCoAttendanceRpt20100211 drop table #tempCoAttendanceRpt20100211 

Grazie per le informazioni fornite originariamente … Sì, finalmente non devo creare tutte queste definizioni di tabelle false (severe) quando si utilizzano dati da un’altra stored procedure o database, e sì, è ansible utilizzare anche i parametri.

Cerca tag di riferimento:

  • SQL 2005 stored procedure nella tabella temporanea

  • openquery con stored procedure e variabili 2005

  • openquery con variabili

  • eseguire la stored procedure nella tabella temporanea

Aggiornamento: questo non funzionerà con le tabelle temporanee quindi ho dovuto ricorrere alla creazione manuale della tabella temporanea.

Avviso di bummer : non funzionerà con tabelle temporanee , http://www.sumrskog.se/share_data.html#OPENQUERY

Riferimento: la prossima cosa è definire LOCALSERVER. Può sembrare una parola chiave nell’esempio, ma in realtà è solo un nome. Ecco come lo fai:

 sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername 

Per creare un server collegato, è necessario disporre dell’authorization ALTER ANY SERVER oppure essere membro di uno qualsiasi dei ruoli del server fisso sysadmin o setupadmin.

OPENQUERY apre una nuova connessione a SQL Server. Ciò ha alcune implicazioni:

La procedura che chiami con OPENQUERY non può fare riferimento a tabelle temporanee create nella connessione corrente.

La nuova connessione ha il proprio database predefinito (definito con sp_addlinkedserver, il default è master), quindi tutte le specifiche dell’object devono includere un nome di database.

Se si dispone di una transazione aperta e si stanno bloccando i blocchi quando si chiama OPENQUERY, la procedura chiamata non può accedere a ciò che si blocca. Cioè, se non stai attento ti bloccerai.

Il collegamento non è gratuito, quindi c’è una penalità per le prestazioni.

Se OPENROWSET ti sta causando problemi, c’è un altro modo dal 2012 in poi; fai uso di sys.dm_exec_describe_first_result_set_for_object, come menzionato qui: recupera i nomi delle colonne e i tipi di una stored procedure?

Innanzitutto, creare questa stored procedure per generare l’SQL per il temporaneo

 CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition( @ProcedureName nvarchar(128), @TableName nvarchar(128), @SQL nvarchar(max) OUTPUT ) AS SET @SQL = 'CREATE TABLE ' + @tableName + ' (' SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +'' + ',' FROM sys.dm_exec_describe_first_result_set_for_object ( OBJECT_ID(@ProcedureName), NULL ); --Remove trailing comma SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL)) SET @SQL = @SQL +')' 

Per utilizzare la procedura, chiamala nel seguente modo:

 DECLARE @SQL NVARCHAR(MAX) exec dbo.usp_GetStoredProcTableDefinition @ProcedureName='dbo.usp_YourProcedure', @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT INSERT INTO ##YourGlobalTempTable EXEC [dbo].usp_YourProcedure select * from ##YourGlobalTempTable 

Si noti che sto usando una tabella temporanea globale. Questo perché l’uso di EXEC per eseguire l’SQL dinamico crea la propria sessione, quindi una normale tabella temporanea non rientra nell’ambito di alcun codice successivo. Se una tabella temporanea globale è un problema, è ansible utilizzare una tabella temporanea ordinaria, ma qualsiasi SQL successivo deve essere dinamico, ovvero eseguito anche dall’istruzione EXEC.

Questo processo memorizzato fa il lavoro:

 CREATE PROCEDURE [dbo].[ExecIntoTable] ( @tableName NVARCHAR(256), @storedProcWithParameters NVARCHAR(MAX) ) AS BEGIN DECLARE @driver VARCHAR(10) DECLARE @connectionString NVARCHAR(600) DECLARE @sql NVARCHAR(MAX) DECLARE @rowsetSql NVARCHAR(MAX) SET @driver = '''SQLNCLI''' SET @connectionString = '''server=' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + ';trusted_connection=yes''' SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + '''' SET @sql = ' SELECT * INTO ' + @tableName + ' FROM OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')' EXEC (@sql) END GO 

È una leggera rielaborazione di ciò: inserisci i risultati della stored procedure nella tabella in modo che funzioni effettivamente.

Se si desidera che funzioni con una tabella temporanea, sarà necessario utilizzare una tabella ##GLOBAL e rilasciarla successivamente.

Se sei abbastanza fortunato da avere SQL 2012 o versioni successive, puoi utilizzare dm_exec_describe_first_result_set_for_object

Ho appena modificato lo sql fornito da gotqn. Grazie gotqn.

Questo crea una tabella temporanea globale con nome uguale al nome della procedura. La tabella temporanea può essere successivamente utilizzata come richiesto. Basta non dimenticare di rilasciarlo prima di rieseguire.

  declare @procname nvarchar(255) = 'myProcedure', @sql nvarchar(max) set @sql = 'create table ##' + @procname + ' (' begin select @sql = @sql + '[' + r.name + '] ' + r.system_type_name + ',' from sys.procedures AS p cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r where p.name = @procname set @sql = substring(@sql,1,len(@sql)-1) + ')' execute (@sql) execute('insert ##' + @procname + ' exec ' + @procname) end 

Per inserire il primo set di record di una stored procedure in una tabella temporanea è necessario sapere quanto segue:

  1. solo il primo set di righe della stored procedure può essere inserito in una tabella temporanea
  2. la stored procedure non deve eseguire istruzioni T-SQL dinamiche ( sp_executesql )
  3. devi prima definire la struttura della tabella temporanea

Quanto sopra può sembrare una limitazione, ma IMHO ha perfettamente senso – se si utilizza sp_executesql è ansible restituire una volta due colonne e una volta dieci e se si dispone di più set di risultati, non è ansible inserirli in più tabelle – è ansible inserire massimo in due tabelle in una istruzione T-SQL (utilizzando la clausola OUTPUT e nessun trigger).

Quindi, il problema è principalmente come definire la struttura temporanea della tabella prima di eseguire l’istruzione EXEC ... INTO ...

  • sys.dm_exec_describe_first_result_set_for_object
  • sys.dm_exec_describe_first_result_set
  • sp_describe_first_result_set

Il primo funziona con OBJECT_ID mentre il secondo e il terzo funzionano anche con query ad hoc. Preferisco utilizzare la DMV invece della sp perché è ansible utilizzare CROSS APPLY e creare contemporaneamente definizioni di tabelle temporanee per più procedure.

 SELECT p.name, r.* FROM sys.procedures AS p CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r; 

Inoltre, prestare attenzione al campo system_type_name poiché può essere molto utile. Memorizza la definizione completa della colonna. Per esempio:

 smalldatetime nvarchar(max) uniqueidentifier nvarchar(1000) real smalldatetime decimal(18,2) 

e puoi usarlo direttamente nella maggior parte dei casi per creare la definizione della tabella.

Quindi, penso che nella maggior parte dei casi (se la procedura memorizzata corrisponde a determinati criteri) puoi facilmente creare istruzioni dinamiche per risolvere tali problemi (creare la tabella temporanea, inserire il risultato della procedura memorizzata, fare ciò che ti serve con i dati) .


Si noti che gli oggetti sopra non riescono a definire i primi dati della serie di risultati in alcuni casi, come quando vengono eseguite istruzioni T-SQL dinamiche o vengono utilizzate tabelle temporanee nella stored procedure.

Codice

 CREATE TABLE #T1 ( col1 INT NOT NULL, col2 NCHAR(50) NOT NULL, col3 TEXT NOT NULL, col4 DATETIME NULL, col5 NCHAR(50) NULL, col6 CHAR(2) NULL, col6 NCHAR(100) NULL, col7 INT NULL, col8 NCHAR(50) NULL, col9 DATETIME NULL, col10 DATETIME NULL ) DECLARE @Para1 int DECLARE @Para2 varchar(32) DECLARE @Para3 varchar(100) DECLARE @Para4 varchar(15) DECLARE @Para5 varchar (12) DECLARE @Para6 varchar(1) DECLARE @Para7 varchar(1) SET @Para1 = 1025 SET @Para2 = N'6as54fsd56f46sd4f65sd' SET @Para3 = N'XXXX\UserName' SET @Para4 = N'127.0.0.1' SET @Para5 = N'XXXXXXX' SET @Para6 = N'X' SET @Para7 = N'X' INSERT INTO #T1 ( col1, col2, col3, col4, col5, col6, col6, col7, col8, col9, col10, ) EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6 

Spero che aiuti. Si prega di qualificarsi come appropriato.

Se la query non contiene parametri, utilizzare OpenQuery altrimenti utilizzare OpenRowset .

La cosa fondamentale sarebbe creare uno schema come da stored procedure e inserirlo in quella tabella. per esempio:

 DECLARE @abc TABLE( RequisitionTypeSourceTypeID INT , RequisitionTypeID INT , RequisitionSourcingTypeID INT , AutoDistOverride INT , AllowManagerToWithdrawDistributedReq INT , ResumeRequired INT , WarnSupplierOnDNRReqSubmission INT , MSPApprovalReqd INT , EnableMSPSupplierCounterOffer INT , RequireVendorToAcceptOffer INT , UseCertification INT , UseCompetency INT , RequireRequisitionTemplate INT , CreatedByID INT , CreatedDate DATE , ModifiedByID INT , ModifiedDate DATE , UseCandidateScheduledHours INT , WeekEndingDayOfWeekID INT , AllowAutoEnroll INT ) INSERT INTO @abc EXEC [dbo].[usp_MySp] 726,3 SELECT * FROM @abc 
  1. Sto creando una tabella con il seguente schema e dati.
  2. Creare una stored procedure.
  3. Ora so qual è il risultato della mia procedura, quindi sto eseguendo la seguente query.

     CREATE TABLE [dbo].[tblTestingTree]( [Id] [int] IDENTITY(1,1) NOT NULL, [ParentId] [int] NULL, [IsLeft] [bit] NULL, [IsRight] [bit] NULL, CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[tblTestingTree] ON INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF 

    VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo]. [TblTestingTree] On

     create procedure GetDate as begin select Id,ParentId from tblTestingTree end create table tbltemp ( id int, ParentId int ) insert into tbltemp exec GetDate select * from tbltemp; 

Ho trovato Passing Arrays / DataTables in stored procedure che potrebbero darti un’altra idea su come potresti risolvere il tuo problema.

The link suggests to use an Image type parameter to pass into the stored procedure. Then in the stored procedure, the image is transformsd into a table variable containing the original data.

Maybe there is a way this can be used with a temporary table.

Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:

 SELECT * FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type)) 

I met the same problem and here is what I did for this from Paul’s suggestion . The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

 DECLARE @sql varchar(max) = '', @tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID()) SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE' EXEC(@sql) EXEC('SELECT * FROM [' + @tmp_global_table + ']') 

If you know the parameters that are being passed and if you don’t have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.

Well, you do have to create a temp table, but it doesn’t have to have the right schema….I’ve created a stored procedure that modifies an existing temp table so that it has the required columns with the right data type and order (dropping all existing columns, adding new columns):

 create procedure TempTableForSP(@tableId int, @procedureId int) as begin declare @tableName varchar(max) = (select name from tempdb.sys.tables where object_id = @tableId ); declare @tsql nvarchar(max); declare @tempId nvarchar(max) = newid(); set @tsql = ' declare @drop nvarchar(max) = (select ''alter table tempdb.dbo.' + @tableName + ' drop column '' + quotename(c.name) + '';''+ char(10) from tempdb.sys.columns c where c.object_id = ' + cast(@tableId as varchar(max)) + ' for xml path('''') ) alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int; exec sp_executeSQL @drop; declare @add nvarchar(max) = ( select ''alter table ' + @tableName + ' add '' + name + '' '' + system_type_name + case when d.is_nullable=1 then '' null '' else '''' end + char(10) from sys.dm_exec_describe_first_result_set_for_object(' + cast(@procedureId as varchar(max)) + ', 0) d order by column_ordinal for xml path('''')) execute sp_executeSQL @add; alter table ' + @tableName + ' drop column ' + quotename(@tempId) + ' '; execute sp_executeSQL @tsql; end 

This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I’d love to know about it.

 DECLARE @storeProcname NVARCHAR(MAX) = '' SET @storeProcname = 'myStoredProc' DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName ' SELECT @strSQL = @strSQL+STUFF(( SELECT ',' +name+' ' + system_type_name FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0) FOR XML PATH('') ),1,1,'(') + ')' EXEC (@strSQL) INSERT INTO myTableName EXEC ('myStoredProc @param1=1, @param2=2') SELECT * FROM myTableName DROP TABLE myTableName 

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.

I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'