Passing a user-defined table type between SQL Server database

Question

I have a user-defined table type in one database in SQL Server (let's call this DB1).

The definition for my type is very simple and just includes 2 columns. The script to create my type is below:

CREATE TYPE [dbo].[CustomList] AS TABLE
(
    [ID] [int] ,
    [Display] [NVARCHAR] (100)  
)

I've also run the same script on another database, so my type is on 2 databases (Let's call the 2nd database DB2).

I now call a stored procedure in DB1 from my C# app passing in the parameter for my CustomList user-defined type.

The procedure in DB1 now needs to call a procedure on DB2 passing in this CustomList.

So, the procedure in DB1 looks like this:

ALTER PROCEDURE [dbo].[selectData]
    @psCustomList CustomList ReadOnly
AS
BEGIN
    EXEC DB2.dbo.selectMoreData @psCustomList   
END

And the procedure in DB2 is like this (I've only show the parameter list as that's all that is needed):

ALTER PROCEDURE [dbo].[selectMoreData]
    @psCustomList CustomList ReadOnly
AS
BEGIN
......

When I run this I receive the following error:

Operand type clash: CustomList is incompatible with CustomList

Anybody got any ideas what I'm doing wrong?

I'm using SQL Server 2008.

Thanks in advance

Answer 1

This is a duplicate of Can you create a CLR UDT to allow for a shared Table type across databases?

Essentially, User-Defined Table Types cannot be shared across databases. CLR-based UDTs can be shared across databases, but only if certain conditions have been met, such as the same Assembly being loaded into both databases, and a few other things (details are in the duplicate question noted above).

For this particular situation, there is a way to pass the info from DB1 to DB2, though it is not an elegant solution. In order to use a Table Type, your current database context needs to be the database in which the Table Type exists. This is done via the USE statement, but that can only be done in dynamic SQL if needing to be done within a Stored Procedure.

USE [DB1];
GO

CREATE PROCEDURE [dbo].[selectData]
    @psCustomList CustomList READONLY
AS
BEGIN
    -- create a temp table as it can be referenced in dynamic SQL
    CREATE TABLE #TempCustomList
    (
        [ID] [INT],
        [Display] [NVARCHAR] (100)
    );

    INSERT INTO #TempCustomList (ID, Display)
        SELECT ID, Display FROM @psCustomList;

    EXEC('
        USE [DB2];

        DECLARE @VarCustomList CustomList;

        INSERT INTO @VarCustomList (ID, Display)
            SELECT ID, Display FROM #TempCustomList;

        EXEC dbo.selectMoreData @VarCustomList;
     ');
END

UPDATE

Using sp_executesql, either in an attempt to avoid the local temporary table by simply passing in the UDTT as a TVP, or simply as a means of doing a parameterized query, does not actually work (though it certainly looks like it should). Meaning, the following:

USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeA
(
    @TheUDTT dbo.TestTable1 READONLY
)
AS
SET NOCOUNT ON;

EXEC sp_executesql N'
  USE [DB2];
  SELECT DB_NAME() AS [CurrentDB];

  DECLARE @TableTypeDB2 dbo.TestTable2;
  INSERT INTO @TableTypeDB2 ([Col1])
    SELECT tmp.[Col1]
    FROM   @TableTypeDB1 tmp;

  --EXEC dbo.CrossDatabaseTableTypeB @TableTypeDB2;
  ',
  N'@TableTypeDB1 dbo.TestTable1 READONLY',
  @TableTypeDB1 = @TheUDTT;
GO


DECLARE @tmp dbo.TestTable1;
INSERT INTO @tmp ([Col1]) VALUES (1), (3);
SELECT * FROM @tmp;

EXEC dbo.CrossDatabaseTableTypeA @TheUDTT = @tmp;

will fail on "@TableTypeDB2 has an invalid datatype", even though it correctly displays that DB2 is the "current" Database. It has something to do with how sp_executesql determines variable datatypes since the error referred to @TableTypeDB2 as "variable # 2", even though it is created locally and not as an input parameter.

In fact, sp_executesql will error if a single variable is declared (via the parameter list input parameter to sp_executesql), even if it is never referenced, let alone used. Meaning, the following code will run into the same error of not being able to find the definition for the UDTT that happens with the query immediately above:

USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeC
AS
SET NOCOUNT ON;

EXEC sp_executesql N'
  USE [DB2];
  SELECT DB_NAME() AS [CurrentDB];

  DECLARE @TableTypeDB2 dbo.TestTable2;
  ',
  N'@SomeVar INT',
  @SomeVar = 1;
GO

(Thanks to @Mark Sowul for mentioning that sp_executesql doesn't work when passing in variables)

HOWEVER, this problem can be worked-around (well, as long as you aren't trying to pass in the TVP in order to avoid the temp table -- 2 queries above) by changing the execution database of sp_executesql so that the process will be local to the DB in which the other TVP exists. One nice thing about sp_executesql is that, unlike EXEC, it is a Stored Procedure, and a system stored procedure at that, so it can be fully qualified. Making use of this fact allows sp_executesql to work, which also means that there is no need for the USE [DB2]; statement within the Dynamic SQL. The following code does work:

USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeD
(
    @TheUDTT dbo.TestTable1 READONLY
)
AS
SET NOCOUNT ON;

-- create a temp table as it can be referenced in dynamic SQL
CREATE TABLE #TempList
(
    [ID] [INT]
);

INSERT INTO #TempList ([ID])
   SELECT [Col1] FROM @TheUDTT;

EXEC [DB2].[dbo].sp_executesql N'
  SELECT DB_NAME() AS [CurrentDB];

  DECLARE @TableTypeDB2 dbo.TestTable2;
  INSERT INTO @TableTypeDB2 ([Col1])
    SELECT tmp.[ID]
    FROM   #TempList tmp;

  EXEC dbo.CrossDatabaseTableTypeB @TableTypeDB2;
  ',
  N'@SomeVariable INT',
  @SomeVariable = 1111;
GO