How to use a data type (table) defined in another database in SQL2k8?

Question

I have a Table Type defined in a database. It is used as a table-valued parameter in a stored procedure. I would like to call this procedure from another database, and in order to pass the parameter, I need to reference this defined type.

But when I do DECLARE @table dbOtherDatabase.dbo.TypeName , it tells me that The type name 'dbOtherDatabase.dbo.TypeName' contains more than the maximum number of prefixes. The maximum is 1.

How could I reference this table type?

Answer 1

Can you not just define the type in both databases.

Edit

See this article on how to do what you require

Answer 2

you could try using sp_executesql:

DECLARE @mylist integer_list_tbltype,
        @sql nvarchar(MAX)
SELECT  @sql = N'SELECT p.ProductID, p.ProductName
                 FROM    Northwind..Products p
                 WHERE   p.ProductID IN (SELECT n FROM @prodids)'
INSERT @mylist VALUES(9),(12),(27),(37)
EXEC sp_executesql @sql, N'@prodids integer_list_tbltype READONLY', @mylist

and if that doesn't work you may have to create a wrapper procedure in the remote DB, where you pass in a CSV string and the wrapper procedure splits it and creates the table (now using the local table type) to then pass into the actual procedure. See this answer for an explanation on how to split a CVS string.

Answer 3

Cross-database user-defined types seems to work only for CLR-based types. See this forum and MSDN (plus comments).