type - sp_executesql sql server

Pasar una variable TABLE a sp_executesql

Question

Estoy tratando de pasar una variable TABLE al procedimiento sp_executesql:

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

 EXEC sp_executesql @sql, @params, @workingData

Me sale el error:

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

Intenté omitir la especificación de la columna después de ''TABLE''. También traté de declarar la tabla como una variable dentro del SQL dinámico. Pero sin suerte ...

Me parece que las variables TABLE no se pueden pasar como parámetros en este procedimiento ?. Por cierto: estoy ejecutando MSSQL2008 R2.

No estoy interesado en usar una tabla temporal local como #workingData porque cargo los datos de trabajo de otro procedimiento:

INSERT INTO @workingData
     EXEC myProc @param1, @param2

Lo cual no puedo hacer directamente en un temp temporal (¿no?) ...

Cualquier ayuda apreciada!

Answer 1

OK, esto me dará lo que quiero, pero seguramente no es bonito:

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

Debe haber una forma mejor de pasar este conjunto de resultados temporales a sp_executesql?

Saludos Alex

Answer 2

Si bien esto puede no responder directamente a su pregunta, debería resolver su problema en general.

De hecho, puede capturar los resultados de una ejecución de Procedimiento almacenado en una tabla temporal:

INSERT INTO #workingData
EXEC myProc 

Así que cambie su código para que se parezca a lo siguiente:

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    

Saludos, Tim

Answer 3

Si está utilizando SQL Server 2008, para pasar una variable de tabla a un procedimiento almacenado, primero debe definir el tipo de tabla, por ejemplo:

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

o use un tipo de tabla existente almacenado en la base de datos.

Use esta consulta para localizar tipos de tablas existentes

SELECT * FROM sys.table_types

Para usar en un procedimiento almacenado, declare una variable de entrada como la tabla:

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

END
GO

Complete la variable de la tabla antes de pasar al procedimiento almacenado:

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

Llame al procedimiento almacenado:

EXECUTE usp_myproc
@TableVariable = @DataTable

Más discusiones here .

Answer 4

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

Tenga en cuenta que el código anterior no funcionará como una tabla ya que un objeto está fuera del alcance. Le dará el error: debe declarar la variable de la tabla. Para evitarlo, podemos hacer lo siguiente.

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