sql server 表类型冲突操作数

问题描述

我在两个不同的数据库模式中定义了相同的表类型.当我尝试从一个架构调用 SP 到另一个将类型作为参数传递时，我收到以下错误:

I have a the same table type defined in two different database schemas. When I try to call a SP from one schema to another passing the type as parameter, I got the following error:

操作数类型冲突 myCustomType 与 myCustomType 不兼容"

我有以下代码:

CREATE TYPE myCustomType AS TABLE
(
  somevalue INT,
  somevalue2 INT
);

存储过程定义

USE DB1
GO
CREATE PROC1(
  @myVar myCustomType READONLY
)
AS
BEGIN
  EXEC db2.dbo.PROC2 @myVar
END

GO

USE DB2
GO
CREATE PROC2(
  @myVar myCustomType READONLY
)
AS
BEGIN
  --do something with myVar
END

执行

USE db1
GO
DECLARE @myVar myCustomType
INSERT into @myVar(1,2)
EXEC PROC1 @myVar

我该如何解决这个问题?

How can I fix this problem?

推荐答案

您遇到了用户定义表类型的限制之一.

You're come up against one of the limitations of the user defined table type.

见这个Microsoft Connect 项目，以设计时"关闭.

See this Microsoft Connect item, closed as "as-designed".

给出的理由是

1. proc 参数的 [table] 类型必须与传入参数的类型完全相同
2. 验证是否未应用规则 (1) 的成本越来越高

在数据库之间传递表类型参数是不可能的，因为你不能使用像

It is impossible to pass table-type parameters between databases, because you cannot use code like

create proc PROC2(
 @myVar db1.dbo.myCustomType READONLY
)

错误是:

类型名称db1.dbo.myCustomType"包含的前缀数超过最大数量.最大值为 1.

仅仅因为您在 DB1 和 DB2 中为它们命名并赋予它们相同的定义，并不能使它们成为相同的类型 - 它们仍然不兼容，就像以下在单个数据库上也失败一样:

Just because you named them the same and gave them the same definition in both DB1 and DB2 does not make them the same type - they remain incompatible, as much as the below which also fails on a single db:

CREATE TYPE myCustomTypeX AS TABLE
(
somevalue INT,
somevalue2 INT
);
GO
create proc procX
@in myCustomTypeX readonly
AS
select * from @in myCustomTypeX;
GO
declare @myCustomTypeX TABLE
(
somevalue INT,
somevalue2 INT
);
exec procX @myCustomTypeX

--
Msg 206, Level 16, State 2, Procedure procX, Line 0
Operand type clash: table is incompatible with myCustomTypeX

这篇关于sql server 表类型冲突操作数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！