将用户定义的表传递给存储过程

I have a User Defined Table that I am passing into a stored procedure from within a stored procedure.

我有一个用户定义表,我从存储过程中传递到存储过程。

DECLARE @tmpInput MyTableType;

--Table is populated from an INPUT XML

exec ValidateInputXML SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute';

Now this isn't giving me an error, but when I run a select from with the ValidateInputXML the table has no data.

现在这不会给我一个错误,但是当我使用ValidateInputXML运行select时,表没有数据。

2 个解决方案

#1

You can also use Table-Valued parameter for your stored procedure. E.g.

您还可以将Table-Valued参数用于存储过程。例如。

/* Create a table type. */
CREATE TYPE MyTableType AS TABLE 
( Column1 VARCHAR(50)
, ........ );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. ValidateInputXML
    @TVP MyTableType READONLY
    AS 
     -- Do what ever you want to do with the table received from caller
    GO

/* Declare a variable that references the type. */
DECLARE @myTable AS MyTableType;

-- Fill @myTable with data and send it to SP. 
insert into @myTable SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute';


/* Pass the table variable data to a stored procedure. */
EXEC ValidateInputXML @myTable ;
GO

#2

The scope of the user defined table is in the stored procedure. Once the stored procedure is executed the table @tmpInput is created and populated and after that you cannot access it.

用户定义表的范围在存储过程中。执行存储过程后,将创建并填充表@tmpInput,之后您将无法访问它。

From the docs:

来自文档:

The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

变量的范围从声明它的位置到声明它的批处理或存储过程的结束。

You have two options:

你有两个选择:

OPTION 1:

Create a table in which you can store the records permanently.

创建一个表,您可以在其中永久存储记录。

OPTION 2:

select the records from inside the stored procedure like :

从存储过程内部选择记录,如:

alter procedure ValidateInputXML 
DECLARE @tmpInput MyTableType;

--Table is populated from an INPUT XML

SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute';

and then

exec ValidateInputXML

#1