Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Creating a User Defined Table Type

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.


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

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...