Victor Ribeiro da Silva Eloy Victor Ribeiro da Silva Eloy - 5 months ago 22
SQL Question

sql server table type clash operand

I have a the same table type defined in two diferent database schemas. When I try to call a SP from one schmema to another passing the type as parameter, I got the folowing error

"Operand type clash myCustomType is incompatible with myCustomType"

the idea is aproximatly these

------------------------------------------------------------the type definition---------------------------------------------------------

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


------------------------------------------------------------the stored procedure definition----------------------------------------------

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


------------------------------------------------------------the execution--------------------------------------------------------

use db1
go
DECLARE @myVar myCustomType
insert into @myVar(1,2)
exec PROC1 @myVar


How can I fix this problem?

Answer

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

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

The reasoning given is that

  1. The [table]type of the proc param must be exactly the type of the incoming param
  2. It becomes increasingly expensive to validate if rule (1) were not applied

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

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

The error is:

The type name 'db1.dbo.myCustomType' contains more than the maximum number of prefixes. The maximum is 1.

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