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