Create a UDT that has SQL @tables as its members

Question

I wonder whether it is possible to create a CLR user-defined type in SQL Server 2008 members of which would be table variables.

You know, you can declare a table variable:

declare @foo table (row_id int not null ... );

So I fancy a UDT with several members, each of which is a table defined that way (a different table, of course), so it'd be possible to say:

select id from @typed_var.table1 where ...

or

insert into @typed_var.table3(id) values (...)

I do have a feeling I want too much on this one, but can't seem to find a definite Yes or No on the Internet.
Is it possible at all?

I think this question is related: http://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement — ypercubeᵀᴹ, Jul 09 '11 at 01:14
And this article has some very useful stuff: http://www.sommarskog.se/dynamic_sql.html#FirstEncounter — ypercubeᵀᴹ, Jul 09 '11 at 01:20

score 1 · Answer 1 · answered Jul 09 '11 at 01:06

No, this is not possible. SQL server will not let you SELECT or INSERT into a property of a UDT.

I tried this, and got the error below:

create type childTable as table
(
    a int,
    b int
)

create type childTable2 as table
(
    c int
)

create type parentTable as table
(
    firstChild childTable,
    secondChild childTable2
)

Msg 350, Level 16, State 1, Line 1
The column "firstChild" does not have a valid data type. A column cannot be of a user-defined table type.

Create a UDT that has SQL @tables as its members

1 Answers1