Sql-server – How to Retrieve value from a User-Defined Table Type (UDTT)

sql-server-2012t-sqluser-defined-table-type

Goal:
I want to reach and display the value of @data inside of [wwtest].

Problem:
The UDTT is from the database [test] and I receive an error message:

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

I don't know how to retrieve the data from the database [test] in a query currently in the database [wwtest]?

enter image description here

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)                        
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'), 
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'), 
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');


insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'), 
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');




CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL    
)
GO



DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol]) 
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);

Best Answer

You need to create the type inside the database where you will declare it - data types can not be reached across databases or servers. This is no different from creating a local variable using an alias type - it has to exist in the context of the current database (or your code has to execute in the context of the database where the type exists, e.g.

EXEC dbname.sys.sp_executesql N'DECLARE @t dbo.type; 
  INSERT @t(cols) SELECT whatever...;
  -- do other stuff but it has to be in this dynamic SQL block';
Related Question