How 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

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

What is the option in SQL to handle nested user defined table type from Oracle?

Now the challenge is one of the table type object needs to be passed on to a stored procedure as a parameter from Java. How can we handle this in SQL server without flattening the structure in Java.

Use JSON. JSON documents (and XML too for that matter) allow arbitrary data shapes to be passed from the client to the SQL Server and consumed there with OPENJSON. And there's no requirement to create user-defined table types for every different data shape you want to pass to the server.

If the Java developers would prefer to use XML, that will work too.

Accessing User Defined Table type from another database

SImple: You can not.

Schema definitions must be from the same database - and a user defined type in your usage scenario is a schema definition.

This is why there is only max. 1 prefix allowed - 1 prefix identifies another schema (within the same database).

So, you will have to copy the type definition over to your other database to use it.

---

Related Topics