SQL Server – User Defined Table Types

User-Defined Table Types
New feature available in SQL Server 2008 and above. You can now pass a table into a stored procedure as a parameter. e.g. You have a List representing table row ids. You want to return a recordset from the database using these ids. A user-defined table type is perfect for this.

Example
We’re going to create a person table and then return some rows from this table using a user-defined table type.

Step 1. Open SQL Server Mgmt Studio
Step 2. Create your person table

CREATE TABLE [dbo].[tbl_person](
[row_id] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](50) NOT NULL,
[lname] [varchar](50) NOT NULL
) ON [PRIMARY]

Step 3. Add some rows

insert into tbl_person (fname, lname) values (‘trevor’, ‘chaplin’), (‘jill’, ‘swinburne’), (‘James’, ‘Bolam’), (‘Barbara’, ‘Flynn’)

Step 4. Create your user-defined table type – IdParametersTableType

USE [workshop]
GO

/****** Object: UserDefinedTableType [dbo].[IdParametersTableType] Script Date: 1/20/2015 6:27:02 PM ******/
CREATE TYPE [dbo].[IdParametersTableType] AS TABLE(
[Id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

The user-defined table type ‘IdParametersTableType’ has a single column named ‘Id’ of type int

Step 5. Create a simple TSQL script to demonstrate usage

declare @p IdParametersTableType
insert @p values (1),(2)
select * from tbl_person where row_id in (select id from @p)

Step 6. Create a simple stored procedure to demonstrate usage

CREATE PROCEDURE GetPersons
(
@PersonIds IdParametersTableType READONLY
)
AS
BEGIN
SET NOCOUNT ON

select row_id, fname, lname
from
tbl_person where row_id in (select id from @PersonIds)

END

declare @p IdParametersTableType
insert @p values (1),(2)
exec GetPersons @p

Note:
Notice the READONLY qualifier after the table parameter. This is required because a copy of the user-defined table variable is not passed to the procedure. To be more efficient, a pointer is passed to the proc. Therefore, to eliminate changes to the variable inside the proc that would affect the original, no changes are allowed.

‹ Clustered & Non Clustered Indexes in SQL Server

CROSS APPLY Operator ›

Posted in Uncategorized