sql server table type clash operand

Question

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:

"Operand type clash myCustomType is incompatible with myCustomType"

I have the following Code:

Type Definition

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

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

Execution

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

How can I fix this problem?

Answer 1

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

Answer 2

I'm using SQL Server 2012 on both databases and this limitation still applies.

There's a great article here full of workarounds: http://www.sommarskog.se/share_data.html

For my immediate problem I've gone with XML to pass a table of data from one proc to another. It's clunky, but I only have to pass a couple of dozen records at a time. Many more than that and I'd start to worry about performance.