在SQL Server数据库间传递用户定义的表类型

Question

在SQL Server数据库间传递用户定义的表类型

sql-serversql-server-2008user-defined-types

10

我在SQL Server的一个数据库（我们称其为DB1）中有一个用户定义的表类型。

我的类型定义非常简单，只包括2列。创建我的类型的脚本如下：

CREATE TYPE [dbo].[CustomList] AS TABLE
(
    [ID] [int] ,
    [Display] [NVARCHAR] (100)  
)

我还在另一个数据库上运行了同样的脚本，所以我的类型在2个数据库上（我们称第二个数据库为DB2）。

现在，我从C#应用程序中调用存储过程，在DB1中传递CustomList用户定义类型的参数。

DB1中的过程现在需要调用DB2上的一个过程，并传递这个CustomList。

因此，DB1中的过程看起来像这样：

ALTER PROCEDURE [dbo].[selectData]
    @psCustomList CustomList ReadOnly
AS
BEGIN
    EXEC DB2.dbo.selectMoreData @psCustomList   
END

而在DB2中，该过程如下所示（我只展示了参数列表，因为这已经足够了）：

ALTER PROCEDURE [dbo].[selectMoreData]
    @psCustomList CustomList ReadOnly
AS
BEGIN
......

运行时收到以下错误：

操作数类型冲突：CustomList 与 CustomList 不兼容

有人知道我做错了什么吗？

我正在使用 SQL Server 2008。

提前致谢。

- Sun

可能是在不同数据库之间传递表值参数到存储过程的重复问题。 - Damien_The_Unbeliever

“所以我的类型在两个数据库上” - 不，你的两个数据库恰好定义了具有相同名称和结构的表类型。它们没有概念是相同的类型。而且你不能定义一个不同数据库类型的变量，因此你甚至不能创建一个正确类型的变量并复制数据。 - Damien_The_Unbeliever

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Solomon Rutzky · Accepted Answer

这是一个重复的问题：Can you create a CLR UDT to allow for a shared Table type across databases?

简而言之，不能跨数据库共享用户定义的表类型。基于CLR的UDT可以在数据库之间共享，但前提条件是已经满足某些特定条件，如相同程序集被加载到两个数据库中，并且有一些其他细节（详见上述重复问题）。

对于这种情况，有一种方法可以将信息从DB1传递到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

更新

使用 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;

尽管正确显示DB2为“当前”数据库，但将在“@TableTypeDB2具有无效数据类型”上失败。这与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 无法工作)

然而，这个问题可以通过改变 sp_executesql 的执行数据库来解决（只要您不是试图传入TVP以避免临时表-在上面的2个查询中）。sp_executesql 的一个好处是，与 EXEC 不同，它是一个存储过程，而且是一个系统存储过程，因此可以完全限定。利用这个特性可以让 sp_executesql 工作，这也意味着在动态 SQL 中不需要 USE [DB2]; 语句。以下代码可以正常工作：

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