Geek asks and answers

Passing a custom table type between a SQL Server database

I have a user-defined table type in a single database in SQL Server (let this DB1).

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

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

I also run the same script in a different database, so my type is in 2 databases (let it call the second database DB2).

Now I call the stored procedure in DB1from my C # application passing in the parameter for my CustomListcustom type.

Now the procedure in DB1should now call the procedure on DB2, passing in it CustomList.

So, the procedure in DB1looks like this:

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

DB2 ( , ):

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

, :

Operand: CustomList CustomList

- , ?

SQL Server 2008.

+5
1

UDT CLR, ?

, . UDT CLR , , , , ( , ).

DB1 DB2, . , , . USE, SQL, .

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

sp_executesql, , UDTT TVP, , ( , , ). :

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;

"@TableTypeDB2 ", , DB2 "" . , sp_executesql , @TableTypeDB2 " № 2", , .

, sp_executesql , ​​ ( sp_executesql), , . , , UDTT, , :

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

( @Mark Sowul , sp_executesql )

HOWEVER, (, TVP, - 2 ), sp_executesql , . sp_executesql , EXEC , . sp_executesql, , USE [DB2]; SQL . :

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
+7

More articles:


All Articles