User Defined Table Types in SQL

So what actually are User defined table types in SQL server? The easiest way to appreciate their value is to think of them as a strongly typed temporary table definition that has the added ability to be passed to a stored procedure as a parameter.

Once used in a procedure or function can’t be altered or dropped so your safe in the knowledge that the type wont be changed after it has been used somewhere.

As UDDTs are data types and unlike temporary tables it is possible to use them as parameter types for a stored procedure or function, which means we can pass record sets from one procedure to another through parameters which makes dealing with complex problems around record sets a little easier to manage.

The UDDTs are passed by reference when used as parameters and because of this they are made read only and to enforce this when the parameters are declared we have to state that they are read only. 

If it is required to manipulate the data in the UDDT parameter then we would need to define a local variable and assign the value of the parameter to it.

CREATE TYPE [dbo].[EmployeeTableType] AS TABLE
(
    [EmployeeID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [SalaryToPay] [decimal](18,2) NULL,
    [SalaryDeductions] [decimal](18,2) NULL,
    [PayDate] [datetime] NULL,
    PRIMARY KEY CLUSTERED 
    (
        [EmployeeID] ASC
    )
)
 
DECLARE @EmployeeList EmployeeTableType
 
INSERT INTO @EmployeeList VALUES(113,'Joe',5000,321,'20140130')
INSERT INTO @EmployeeList VALUES(2123,'Bert',3400,32.31,'20140130')
INSERT INTO @EmployeeList VALUES(1232,'Mary',5500,129,'20140130')
 
UPDATE @EmployeeList SET Name = 'Madge' where  EmployeeID = 1232
 
GO
 
CREATE PROCEDURE TableDataTypeExample(@EmployeeListParm EmployeeTableType READONLY)
 
AS
 
    SELECT * FROM @EmployeeListParm order by EmployeeID
 
GO
 
EXEC TableDataTypeExample @EmployeeList

Note: Need to make sure the type type has the right access permissions.

GRANT REFERENCES, EXECUTE ON TYPE::dbo.EmployeeTableType TO MyRole
GO

Sources:

Why use user defined table types in SQL?