将表值参数传递给跨不同数据库的存储过程 [英] Passing Table Valued parameter to stored procedure across different databases

问题描述

我使用的是 SQL Server 2008.

如何将表值参数传递给不同数据库但同一服务器的存储过程?

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

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

Should I create the same table type in both databases?

请根据问题给出示例或链接.

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

感谢您的任何帮助.

推荐答案

响应此评论(如果我是正确的并且无法在数据库之间使用 TVP):

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

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

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

纯粹的方法是说，如果两个数据库都在处理相同的数据，那么它们应该合并到一个数据库中.实用主义者意识到这并不总是可能的 - 但由于您显然可以更改调用者和被调用者，也许只需使用两个存储过程都知道的临时表.

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.

<小时>

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

---

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.

因此，您在调用方中创建临时表:

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

然后在被调用者中:

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

这篇关于将表值参数传递给跨不同数据库的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！