Passer une variable TABLE à SP executesql

j'essaie de passer une variable TABLE à la procédure sp_executesql:

 DECLARE @params NVARCHAR(MAX)
 SET @params = '@workingData TABLE ( col1 VARCHAR(20),
                col2 VARCHAR(50) )'

 EXEC sp_executesql @sql, @params, @workingData

j'obtiens l'erreur:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.

j'ai essayé d'omettre la spécification de la colonne après 'TABLE'. J'ai également essayé de déclarer la table comme une variable à l'intérieur du SQL dynamique. Mais pas de chance...

me semble que les variables de TABLE ne sont pas autorisées à être passées comme paramètres dans cette procédure?. BTW: j'exécute MSSQL2008 R2.

Je ne suis pas intéressé à utiliser un intérimaire local table comme #workingData parce que je charge les données à partir d'une autre procédure:

INSERT INTO @workingData
     EXEC myProc @param1, @param2

ce que je ne peux pas faire directement dans une variable temporaire (d'accord?)...

Toute aide appréciée!

22
demandé sur Brian Tompsett - 汤莱恩 2010-11-23 20:13:23

4 réponses

si vous utilisez SQL Server 2008, pour passer une variable table à une procédure stockée, vous devez d'abord définir le type de table, par exemple:

CREATE TYPE SalesHistoryTableType AS TABLE
(                     
    [Product] [varchar](10) NULL,                
    [SaleDate] [datetime] NULL,                
    [SalePrice] [money] NULL
)
GO

ou utilisez un type de table existant stocké dans la base de données.

utilisez cette requête pour localiser les types de table existants

SELECT * FROM sys.table_types

Pour une utilisation dans une procédure stockée, déclarer une variable d'entrée à la table:

CREATE PROCEDURE usp_myproc
(
    @TableVariable SalesHistoryTableType READONLY
)
AS BEGIN
    --Do stuff     

END
GO

remplir la variable table avant de passer à la variable stockée procédure:

DECLARE @DataTable AS SalesHistoryTableType
INSERT INTO @DataTable
SELECT * FROM (Some data)

Appel de la procédure stockée:

EXECUTE usp_myproc
@TableVariable = @DataTable

autres discussions ici.

12
répondu Gary Kindel 2015-11-23 15:52:15

OK, cela va me faire ce que je veux, mais sûrement, n'est pas assez:

DECLARE @workingData TABLE ( col1 VARCHAR(20),
        col2 VARCHAR(20) )

    INSERT INTO @workingData
        EXEC myProc

    /* Unfortunately table variables are outside scope
       for the dynamic SQL later run. We copy the 
       table to a temp table. 
       The table variable is needed to extract data directly
       from the strored procedure call above...
    */
    SELECT * 
    INTO #workingData
    FROM @workingData


        DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT * FROM #workingData'

    EXEC sp_executesql @sql

il doit y avoir un meilleur moyen de passer cet ensemble de résultats temporaires dans sp_executesql!?

ce qui Concerne Alex

3
répondu Alex 2010-11-24 07:58:51

bien que cela ne réponde pas directement à votre question, il devrait résoudre votre problème dans l'ensemble.

vous pouvez en effet capturer les résultats de l'exécution D'une procédure stockée dans une table temporaire:

INSERT INTO #workingData
EXEC myProc 

alors changez votre code pour ressembler à ce qui suit:

CREATE TABLE #workingData ( col1 VARCHAR(20),    
    col2 VARCHAR(20) )    

INSERT INTO #workingData    
    EXEC myProc    

DECLARE @sql NVARCHAR(MAX)    
SET @sql = 'SELECT * FROM #workingData'    

EXEC sp_executesql @sql    

ce qui Concerne, Tim

2
répondu Tim Friesen 2011-11-25 18:20:24
Alter PROCEDURE sp_table_getcount 
 @tblname nvarchar(50) ,
 @totalrow int output 
AS
BEGIN

Declare @params nvarchar(1000)
Declare @sql nvarchar(1000)
set @sql = N'Select @cnt= count(*) From @tbl'
set @params = N'@tbl nvarchar(50) , @cnt int OUTPUT'
Exec sp_executesql @sql , @params ,@tbl=@tblname ,  @cnt = @totalrow OUTPUT   
END
GO

veuillez noter que le code ci-dessus ne fonctionnera pas comme table car un objet est hors de la portée.Il vous donnera l'erreur: doit déclarer la variable de table.Afin de travailler autour de nous, nous pouvons faire ce qui suit.

Alter PROCEDURE sp_table_getcount 
 @tblname nvarchar(50) ,
 @totalrow int output 
AS
BEGIN

Declare @params nvarchar(1000)
Declare @sql nvarchar(1000)
set @sql = N'Select @cnt= count(*) From dbo.' + @tblname
set @params = N'@cnt int OUTPUT'
Exec sp_executesql @sql , @params , @cnt = @totalrow OUTPUT   
END
GO
0
répondu kta 2012-05-16 04:32:31