将Table Valued参数传递给跨不同数据库的存储过程

I'm using SQL Server 2008.

我正在使用SQL Server 2008。

How can I pass Table Valued parameter to a Stored procedure across different Databases, but same server?

如何将Table Valued参数传递到跨不同数据库的存储过程，但是相同的服务器？

Should I create the same table type in both databases?

我应该在两个数据库中创建相同的表类型吗？

Please, give an example or a link according to the problem.

请根据问题举例或链接。

Thanks for any kind of help.

谢谢你的帮助。

2 个解决方案

#1

In response to this comment (if I'm correct and that using TVPs between databases isn't possible):

回应此评论（如果我是正确的，并且无法在数据库之间使用TVP）：

What choice do I have in this situation? Using XML type?

在这种情况下我有什么选择？使用XML类型？

The purist approach would be to say that if both databases are working with the same data, they ought to be merged into a single database. The pragmatist realizes that this isn't always possible - but since you can obviously change both the caller and callee, maybe just use a temp table that both stored procs know about.

纯粹的方法是说如果两个数据库都使用相同的数据，它们应该合并到一个数据库中。实用主义者意识到这并不总是可行的 - 但是因为你可以明显地改变调用者和被调用者，所以可能只使用存储过程都知道的临时表。

I don't believe it's possible - you can't reference a table type from another database, and even with identical type definitions in both DBs, a value of one type isn't assignable to the other.

我不相信它是可能的 - 您不能从另一个数据库引用表类型，即使在两个DB中具有相同的类型定义，一个类型的值也不能分配给另一个。

You don't pass the temp table between databases. A temp table is always stored in tempdb, and is accessible to your connection, so long as the connection is open and the temp table isn't dropped.

您不在数据库之间传递临时表。临时表始终存储在tempdb中，并且只要连接打开且临时表未被删除，您的连接就可以访问它。

So, you create the temp table in the caller:

因此，您在调用者中创建临时表：

CREATE TABLE #Values (ID int not null,ColA varchar(10) not null)
INSERT INTO #Values (ID,ColA)
/* Whatever you do to populate the table */
EXEC OtherDB..OtherProc

And then in the callee:

然后在被叫方：

CREATE PROCEDURE OtherProc
/* No parameter passed */
AS
    SELECT * from #Values

#2

Table UDTs are only valid for stored procs within the same database.

表UDT仅对同一数据库中的存储过程有效。

So yes you would have to create the type on each server and reference it in the stored procs - e.g. just run the first part of this example in both DBs http://msdn.microsoft.com/en-us/library/bb510489.aspx.

所以是的，你必须在每个服务器上创建类型并在存储过程中引用它 - 例如只需在两个DB中运行此示例的第一部分http://msdn.microsoft.com/en-us/library/bb510489.aspx。

If you don't need the efficency you can always use other methods - i.e. pass an xml document parameter or have the s.p. expect a temp table with the input data.

如果你不需要效率，你可以随时使用其他方法 - 即传递xml文档参数或拥有s.p.期望带有输入数据的临时表。

Edit: added example

编辑：添加示例

create database Test1
create database Test2
go
use Test1
create type PersonalMessage as TABLE
(Message varchar(50))
go
create proc InsertPersonalMessage @Message PersonalMessage READONLY AS
  select * from @Message
go
use Test2
create type PersonalMessage as TABLE
(Message varchar(50))
go
create proc InsertPersonalMessage @Message PersonalMessage READONLY AS
  select * from @Message
go
use Test1
declare @mymsg PersonalMessage
insert @mymsg select 'oh noes'
exec InsertPersonalMessage @mymsg 
go
use Test2
declare @mymsg2 PersonalMessage
insert @mymsg2 select 'oh noes'
exec InsertPersonalMessage @mymsg2

Disadvantage is that there are two copies of the data. But you would be able to run the batch against each database simultaneously. Whether this is any better than using a table table is really down to what processing/data sizes you have - btw to use a temp table from an s.p. you just access it from the s.p. code (and it fails if it doesn't exist).

缺点是有两个数据副本。但是您可以同时对每个数据库运行批处理。这是否比使用表格更好，实际上取决于你拥有的处理/数据大小 - 顺便使用来自s.p.的临时表。你只需从s.p.访问它。代码（如果它不存在则失败）。

#1