Skip to content

Creating a User Defined Table Type

September 19, 2012

I saw a post about a user defined table type in SQL Server and I was sure it was a typo. I kept thinking the poster meant table variable, but when I searched the term in Books Online, I was surprised to find User-Defined Table Types as an entry.

These types are essentially templates that you can build for easier code reuse. They work in procedures and functions, or even as table variables. The CREATE TABLE syntax includes allowances for using these types.

I can see this as being valuable when you have a structure that you want to pass into a module of some sort in multiple places and don’t want to have to include the code each time. I’m not sure it’s a great benefit, but it does prevent subtle mismatches like one module using varchar(50) for a column and another using varchar(200).

A simple create for this type would be:

CREATE TYPE StateTbl AS TABLE
( StateID INT
, StateCode VARCHAR(2)
, StateName VARCHAR(200)
)
;

This gives me a template I can use. Note that I can’t add rows to this table:

INSERT StateTbl SELECT 1, 'CO', 'Colorado';

I get this error:

Msg 208, Level 16, State 1, Line 1

Invalid object name ‘StateTbl’.

It’s not an object yet. I need to instantiate an object based on this template. I can do that in a procedure:

CREATE PROCEDURE SortStates
  @S StateTbl READONLY
 as

SELECT StateName
 FROM @s
 ORDER BY StateName
RETURN 0
;
GO

Fairly simple stuff. I can easily call this procedure, but I need a set of parameters first.

DECLARE @p TABLE (id INT, scode VARCHAR(3), sname VARCHAR(20))

INSERT @p
 VALUES (1, 'NC', 'North Carolina')
      , (2, 'VA', 'Virginia')
      , (3, 'CO', 'Colorado')
; 
EXEC SortStates @p

However this doesn’t work. The table isn’t compatible (I did that on purpose). Let’s clean it up.

DECLARE @p TABLE StateTbl
   (StateID INT
   , StateCode VARCHAR(2)
   , StateName VARCHAR(200))

INSERT @p
 VALUES (1, 'NC', 'North Carolina')
      , (2, 'VA', 'Virginia')
      , (3, 'CO', 'Colorado')
; 
EXEC SortStates @p

It still doesn’t work. There’s a binding here. I need to use the (cleaner) AS syntax for declaration.

DECLARE @p as StateTbl

INSERT @p
 VALUES (1, 'NC', 'North Carolina')
      , (2, 'VA', 'Virginia')
      , (3, 'CO', 'Colorado')
; 
EXEC SortStates @p

This returns results:

udtt_1

This means that you can use these types to create cleaner code, and enforce some standards (preventing things like people declaring columns with different lengths. However it also means that you have another “type” to manage and ensure everyone is using.

I’m not sure how useful this is, but it is a neat little construct.

About these ads

From → Blog

2 Comments
  1. I recently read about this myself. My understanding is that it is the only way you can pass a table valued parameter into a stored procedure. Do you know if this is correct? It’s the method used in BOL but I wasn’t sure if there was another method as well.

    And actually I can see several very useful cases for this. Possibly the best being all those anoying cases where you pass in a variable with a list of values, then you have to use dynamic SQL because “field IN (@variable)” doesn’t work. Now you can pass it in as a table and just do a join. Might even be faster too.

  2. According to BOL, all TVPs must be declared like this. I hadn’t noticed this was required, though that’s how I’ve used them.

    I thought this was very interesting, and I like the concept. Certainly there are going to be some good use cases even outside of parameters.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,768 other followers

%d bloggers like this: