sql-server – 将用户定义的表传递给存储过程

前端之家收集整理的这篇文章主要介绍了sql-server – 将用户定义的表传递给存储过程前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个用户定义表,我从存储过程中传递到存储过程.
DECLARE @tmpInput MyTableType;

--Table is populated from an INPUT XML

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

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

解决方法

您还可以将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

猜你在找的MsSQL相关文章