sql - variable - select from procedure stockée




Insérer les résultats d'une procédure stockée dans une table temporaire (16)

La solution la plus simple:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Si vous ne connaissez pas le schéma, vous pouvez effectuer les opérations suivantes. S'il vous plaît noter qu'il existe des risques de sécurité graves dans cette méthode.

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

Comment faire un SELECT * INTO [temp table] FROM [stored procedure] ? Pas FROM [Table] et sans définir [temp table] ?

Select toutes les données de BusinessLine dans tmpBusLine fonctionne très bien.

select *
into tmpBusLine
from BusinessLine

J'essaie la même chose, mais l'utilisation d'une stored procedure qui renvoie des données n'est pas tout à fait la même chose.

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

Message de sortie:

Msg 156, niveau 15, état 1, ligne 2 Syntaxe incorrecte près du mot clé 'exec'.

J'ai lu plusieurs exemples de création d'une table temporaire avec la même structure que la procédure stockée en sortie, ce qui fonctionne bien, mais ce serait bien de ne pas fournir de colonnes.


  1. Je crée une table avec le schéma et les données suivants.
  2. Créer une procédure stockée
  3. Maintenant, je sais quel est le résultat de ma procédure, donc j'effectue la requête suivante.

    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
    
    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
    

Ceci est une réponse à une version légèrement modifiée de votre question. Si vous pouvez abandonner l'utilisation d'une procédure stockée pour une fonction définie par l'utilisateur, vous pouvez utiliser une fonction définie par l'utilisateur inline. C'est essentiellement une procédure stockée (prendra des paramètres) qui retourne une table en tant que jeu de résultats; et sera donc bien placé avec une déclaration INTO.

Voici un bon article rapide sur ce sujet et d'autres fonctions définies par l'utilisateur. Si vous avez toujours besoin d'une procédure stockée, vous pouvez envelopper la fonction inline définie par l'utilisateur avec une procédure stockée. La procédure stockée transmet simplement les paramètres lorsqu'elle appelle select * à partir de la fonction inline définie par l'utilisateur.

Ainsi, par exemple, vous auriez une fonction définie par l'utilisateur in-table, définie par l'utilisateur, pour obtenir une liste de clients pour une région particulière:

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

Vous pouvez ensuite appeler cette fonction pour obtenir ce que vos résultats sont tels:

SELECT * FROM CustomersbyRegion(1)

Ou faire un SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Si vous avez toujours besoin d'une procédure stockée, enveloppez la fonction en tant que telle:

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

Je pense que c'est la méthode la plus «sans hack» pour obtenir les résultats souhaités. Il utilise les fonctionnalités existantes car elles étaient destinées à être utilisées sans complications supplémentaires. En imbriquant la fonction définie par l'utilisateur in-table dans la procédure stockée, vous avez accès à la fonctionnalité de deux manières. Plus! Vous avez seulement un point de maintenance pour le code SQL réel.

L'utilisation d'OPENROWSET a été suggérée, mais ce n'est pas ce à quoi la fonction OPENROWSET a été destinée (De la documentation en ligne):

Inclut toutes les informations de connexion requises pour accéder aux données distantes à partir d'une source de données OLE DB. Cette méthode est une alternative à l'accès aux tables dans un serveur lié et est une méthode unique ponctuelle de connexion et d'accès aux données distantes à l'aide de OLE DB. Pour des références plus fréquentes aux sources de données OLE DB, utilisez plutôt des serveurs liés.

L'utilisation d'OPENROWSET fera le travail, mais elle entraînera des frais supplémentaires pour l'ouverture des connexions locales et la collecte des données. Il peut également ne pas être une option dans tous les cas, car il nécessite une autorisation de requête ad hoc qui pose un risque de sécurité et peut donc ne pas être souhaitée. En outre, l'approche OPENROWSET empêchera l'utilisation de procédures stockées renvoyant plusieurs jeux de résultats. Le fait d'emballer plusieurs fonctions définies par l'utilisateur de valeur table en ligne dans une seule procédure stockée peut y parvenir.


Dans SQL Server 2005, vous pouvez utiliser INSERT INTO ... EXEC pour insérer le résultat d'une procédure stockée dans une table. De la documentation INSERT de MSDN (pour SQL Server 2000, en fait):

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

Lorsque la procédure stockée renvoie un grand nombre de colonnes et que vous ne voulez pas "créer" manuellement une table temporaire pour contenir le résultat, j'ai trouvé le moyen le plus simple d'entrer dans la procédure stockée et d'ajouter une clause "into" sur le dernière instruction select et ajoutez 1 = 0 à la clause where.

Exécutez la procédure stockée une fois et revenez en arrière et supprimez le code SQL que vous venez d'ajouter. Maintenant, vous aurez une table vide correspondant au résultat de la procédure stockée. Vous pouvez soit "script table as create" pour une table temporaire, soit simplement l'insérer directement dans cette table.


Pour insérer le premier ensemble d'enregistrements d'une procédure stockée dans une table temporaire, vous devez savoir ce qui suit:

  1. seul le premier ensemble de lignes de la procédure stockée peut être inséré dans une table temporaire
  2. la procédure stockée ne doit pas exécuter l'instruction T-SQL dynamique ( sp_executesql )
  3. vous devez d'abord définir la structure de la table temporaire

Ce qui précède peut sembler une limitation, mais à sp_executesql , il est parfaitement logique - si vous utilisez sp_executesql vous pouvez retourner deux colonnes et une fois dix, et si vous avez plusieurs ensembles de résultats, vous ne pouvez pas les insérer dans plusieurs tables - vous pouvez insérer maximum dans deux tables dans une instruction T-SQL (en utilisant la clause OUTPUT et aucun déclencheur).

Ainsi, le problème consiste principalement à définir la structure de la table temporaire avant d'exécuter l'instruction EXEC ... INTO ...

Le premier fonctionne avec OBJECT_ID tandis que le second et le troisième fonctionnent avec des requêtes Ad-hoc. Je préfère utiliser le DMV au lieu du sp, car vous pouvez utiliser CROSS APPLY et créer les définitions de tables temporaires pour plusieurs procédures en même temps.

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;

En outre, faites attention au champ system_type_name car cela peut être très utile. Il stocke la définition complète de la colonne. Par exemple:

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

et vous pouvez l'utiliser directement dans la plupart des cas pour créer la définition de la table.

Donc, je pense que dans la plupart des cas (si la procédure stockée correspond à certains critères) vous pouvez facilement créer des instructions dynamiques pour résoudre ces problèmes (créer la table temporaire, insérer le résultat de la procédure stockée, faire ce dont vous avez besoin) .

Notez que les objets ci-dessus ne parviennent pas à définir les premières données de jeu de résultats dans certains cases lorsque des instructions T-SQL dynamiques sont exécutées ou que des tables temporaires sont utilisées dans la procédure stockée.


Si l'OPENROWSET vous cause des problèmes, il y a un autre moyen à partir de 2012; faire usage de sys.dm_exec_describe_first_result_set_for_object, comme mentionné ici: Récupérer les noms de colonnes et les types d'une procédure stockée?

D'abord, créez cette procédure stockée pour générer le SQL pour le temporaire

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 +')'

Pour l'utiliser, appelez-le de la façon suivante:

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

Notez que j'utilise une table temporaire globale. En effet, l'utilisation d'EXEC pour exécuter le SQL dynamique crée sa propre session, de sorte qu'une table temporaire ordinaire serait hors de portée pour tout code ultérieur. Si une table temporaire globale est un problème, vous pouvez utiliser une table temporaire ordinaire, mais tout SQL ultérieur devra être dynamique, c'est-à-dire également exécuté par l'instruction EXEC.


Si la requête ne contient pas de paramètre, utilisez OpenQuery else, utilisez OpenRowset .

Chose de base serait de créer un schéma selon la procédure stockée et insérer dans cette table. par exemple:

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

Si vous avez la chance d'avoir SQL 2012 ou supérieur, vous pouvez utiliser dm_exec_describe_first_result_set_for_object

Je viens d'éditer le sql fourni par gotqn. Merci gotqn.

Cela crée une table temporaire globale avec le même nom que le nom de la procédure. La table temporaire peut ensuite être utilisée selon les besoins. N'oubliez pas de le laisser tomber avant de le ré-exécuter.

    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

Si vous voulez le faire sans d'abord déclarer la table temporaire, vous pouvez essayer de créer une fonction définie par l'utilisateur plutôt qu'une procédure stockée et rendre cette fonction définie par l'utilisateur retourne une table. Alternativement, si vous voulez utiliser la procédure stockée, essayez quelque chose comme ceci:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

Vous pouvez utiliser OPENROWSET pour cela. Regarde. J'ai également inclus le code sp_configure pour activer les requêtes distribuées ad hoc, au cas où il n'est pas déjà activé.

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

Code

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

J'espère que ça aide. S'il vous plaît qualifier comme approprié.


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 + ']')

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'


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.


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






stored-procedures