将用户定义的表传递给存储过程 [英] Pass A User-Defined Table to a Stored Procedure
本文介绍了将用户定义的表传递给存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个用户定义的表,我将它从存储过程中传递给存储过程.
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';
现在这并没有给我一个错误,但是当我使用 ValidateInputXML 运行 select from 时,该表没有数据.
Now this isn't giving me an error, but when I run a select from with the ValidateInputXML the table has no data.
推荐答案
您还可以为存储过程使用表值参数.例如.
You can also use Table-Valued parameter for your stored procedure. E.g.
/* 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
这篇关于将用户定义的表传递给存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文