这是重复的您能否创建 CLR UDT 以允许跨数据库共享表类型? https://stackoverflow.com/questions/8036713/can-you-create-a-clr-udt-to-allow-for-a-shared-table-type-across-databases
本质上,用户定义的表类型不能跨数据库共享。基于 CLR 的 UDTcan可以跨数据库共享,但前提是满足某些条件,例如将相同的程序集加载到两个数据库中,以及其他一些事情(详细信息在上面提到的重复问题中)。
对于这种特殊情况,有一种方法可以传递信息DB1
to DB2
,尽管这不是一个优雅的解决方案。为了使用表类型,您当前的数据库上下文必须是该表类型所在的数据库。这是通过USE
语句,但如果需要在存储过程中完成,则只能在动态 SQL 中完成。
USE [DB1];
GO
CREATE PROCEDURE [dbo].[selectData]
@psCustomList CustomList READONLY
AS
BEGIN
-- create a temp table as it can be referenced in dynamic SQL
CREATE TABLE #TempCustomList
(
[ID] [INT],
[Display] [NVARCHAR] (100)
);
INSERT INTO #TempCustomList (ID, Display)
SELECT ID, Display FROM @psCustomList;
EXEC('
USE [DB2];
DECLARE @VarCustomList CustomList;
INSERT INTO @VarCustomList (ID, Display)
SELECT ID, Display FROM #TempCustomList;
EXEC dbo.selectMoreData @VarCustomList;
');
END
UPDATE
Using sp_executesql
,无论是试图通过简单地将 UDTT 作为 TVP 传递来避免本地临时表,还是简单地作为执行参数化查询的一种方法,实际上都不起作用(尽管看起来确实应该如此)。含义如下:
USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeA
(
@TheUDTT dbo.TestTable1 READONLY
)
AS
SET NOCOUNT ON;
EXEC sp_executesql N'
USE [DB2];
SELECT DB_NAME() AS [CurrentDB];
DECLARE @TableTypeDB2 dbo.TestTable2;
INSERT INTO @TableTypeDB2 ([Col1])
SELECT tmp.[Col1]
FROM @TableTypeDB1 tmp;
--EXEC dbo.CrossDatabaseTableTypeB @TableTypeDB2;
',
N'@TableTypeDB1 dbo.TestTable1 READONLY',
@TableTypeDB1 = @TheUDTT;
GO
DECLARE @tmp dbo.TestTable1;
INSERT INTO @tmp ([Col1]) VALUES (1), (3);
SELECT * FROM @tmp;
EXEC dbo.CrossDatabaseTableTypeA @TheUDTT = @tmp;
将在“@TableTypeDB2 有无效数据类型”时失败,即使它正确显示DB2
是“当前”数据库。这和如何做有关系sp_executesql
确定变量数据类型,因为错误引用@TableTypeDB2
作为“变量#2”,即使它是本地创建的而不是作为输入参数。
实际上,sp_executesql
如果声明单个变量(通过参数列表输入参数到sp_executesql
),即使它从未被引用,更不用说使用了。这意味着,以下代码将遇到与上面的查询相同的错误,即无法找到 UDTT 的定义:
USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeC
AS
SET NOCOUNT ON;
EXEC sp_executesql N'
USE [DB2];
SELECT DB_NAME() AS [CurrentDB];
DECLARE @TableTypeDB2 dbo.TestTable2;
',
N'@SomeVar INT',
@SomeVar = 1;
GO
(感谢@Mark Sowul 提到sp_executesql
传入变量时不起作用)
但是,这个问题可以通过更改执行数据库来解决(好吧,只要您不尝试传递 TVP 以避免临时表 - 上面的 2 个查询)sp_executesql
这样该进程将位于另一个 TVP 所在的数据库的本地进程。一件好事sp_executesql
是这样,不像EXEC
,它是一个存储过程,而且是一个系统存储过程,因此它可以是完全限定的。利用这一事实可以sp_executesql
去工作,这也意味着不需要USE [DB2];
动态 SQL 中的语句。下面的代码确实有效:
USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeD
(
@TheUDTT dbo.TestTable1 READONLY
)
AS
SET NOCOUNT ON;
-- create a temp table as it can be referenced in dynamic SQL
CREATE TABLE #TempList
(
[ID] [INT]
);
INSERT INTO #TempList ([ID])
SELECT [Col1] FROM @TheUDTT;
EXEC [DB2].[dbo].sp_executesql N'
SELECT DB_NAME() AS [CurrentDB];
DECLARE @TableTypeDB2 dbo.TestTable2;
INSERT INTO @TableTypeDB2 ([Col1])
SELECT tmp.[ID]
FROM #TempList tmp;
EXEC dbo.CrossDatabaseTableTypeB @TableTypeDB2;
',
N'@SomeVariable INT',
@SomeVariable = 1111;
GO