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:

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.

Filed under: Blog Tagged: sql server, syndicated, T-SQL