跨数据库调用含用户定义表类型的存储过程报错如何解决？

问题背景 #

在数据库dbA中创建了用户定义表类型和存储过程：

USE dbA
GO

CREATE TYPE [dbo].Test AS TABLE(
    Col INT
)
GO

CREATE Procedure TestProc 
(
    @param [dbo].Test READONLY
)
AS
BEGIN
    PRINT 'Blah'
END

在dbA内部调用该存储过程正常：

declare @udt dbo.Test
INSERT INTO @udt(Col) Values(1)
EXEC dbo.TestProc @param = @udt

为了从数据库dbB调用该存储过程，在dbB中创建了同名同结构的用户定义表类型：

USE dbB
GO

CREATE TYPE [dbo].Test AS TABLE(
    Col INT
)
GO

但执行以下调用语句时出现错误：

USE dbB
GO

declare @udt dbo.Test
INSERT INTO @udt(Col) Values(1)

EXEC dbA.dbo.TestProc @param = @udt

错误信息：

Msg 206, Level 16, State 2, Procedure dbA.dbo.TestProc,
Line 0 [Batch Start Line 11] Operand type clash: Test is incompatible
with Test

解决方案 #

SQL Server中，用户定义表类型是数据库级别的对象，即使同名同结构，不同数据库中的该类型也被视为完全独立的对象，因此直接传递dbB的Test类型参数给dbA的存储过程会触发类型冲突。可通过以下两种方式解决：

方法一：直接引用dbA的用户定义表类型 #

无需在dbB中创建同名类型，直接在dbB会话里声明dbA.dbo.Test类型的变量，再传递给存储过程：

USE dbB
GO

declare @udt dbA.dbo.Test
INSERT INTO @udt(Col) Values(1)

EXEC dbA.dbo.TestProc @param = @udt

此方式要求调用者拥有dbA.dbo.Test类型的访问权限。

方法二：通过临时表中转数据 #

如果无法直接引用dbA的类型，可先将数据存入临时表，再通过动态SQL切换到dbA上下文读取数据并调用存储过程：

USE dbB
GO

-- 创建临时表并插入数据
CREATE TABLE #TempTest(Col INT)
INSERT INTO #TempTest(Col) Values(1)

-- 动态SQL切换到dbA上下文，读取临时表数据并调用存储过程
EXEC('
USE dbA
DECLARE @udt dbo.Test
INSERT INTO @udt(Col) SELECT Col FROM #TempTest
EXEC dbo.TestProc @param = @udt
')

-- 清理临时表
DROP TABLE #TempTest

注：临时表是会话级对象，跨数据库动态SQL中可以正常访问。

内容的提问来源于stack exchange，提问作者Eric Klaus