CODEWITHSUNDEEP
sqlsql-serversqldatatypesuser-defined-types
Pradeep Kolli
Pradeep Kolli

Reputation: 33

Is it possible to use user defined table type inside another user defined table type in sql

Is it possible to use user defined table type inside another user defined table type in SQL (nested user define table types).

CREATE TYPE A AS TABLE
(
    A_Id int
)

GO


CREATE TYPE B AS TABLE
(
    B_Id int,
    A_Id A --Need To Use A as data type in B
)
GO

I have senior to send the data table of rows with in a table.

Table
1.Item1
   1.Vendor1
   2.Vendor2
2.Item1
   1.Vendor1
   2.Vendor2

Please help how to send the table data within table rows from asp.net to sql. through looping or is there any simple way to send the nested table data to server.

Upvotes: 2

Views: 6537

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

No. Why would it be? That's not how SQL Server (or any relational database, for that matter) work.

From TechNet's page on User-Defined Table Types:

Restrictions

User-defined table types have the following restrictions:

  • A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

"Nesting" in relational databases are achieved by using Foreign keys

You can't even create a foreign key constraint between two user defined table types.

What you can do is create two table types that one of them have a column to keep the id of the other, like this:

CREATE TYPE A AS TABLE
(
    A_Id int
)

GO


CREATE TYPE B AS TABLE
(
    B_Id int,
    A_Id int -- "FK"
)
GO

Upvotes: 6

Related Questions