Tag: T-SQL

An interesting idea. I saw this question asked after I was playing with table types a bit. “Can you move a table type between schemas?”

Suppose I had two schemas:

CREATE SCHEMA OldSchema
;
GO
CREATE SCHEMA NewSchema
;
GO

In one of them, I create a table type and a procedure:

CREATE TYPE OldSchema.MyTable AS TABLE
( IDCode INT
, Location VARCHAR(200)
)
;

CREATE PROCEDURE OldSchema.MyProc 
AS
 SELECT * FROM dbo.MyLogger
;

There’s nothing fancy here. Just two objects created in one schema. I now have the need to move these to the other schema. Perhaps it’s a mistake. Perhaps I have developers working in one schema and I do integration testing in the other schema. In any case, it’s easy to move the proc with the ALTER SCHEMA syntax:

ALTER SCHEMA NewSchema TRANSFER OldSchema.MyProc
;

I can easily script something to move multiple procs, but if I do this:

ALTER SCHEMA NewSchema TRANSFER OldSchema.MyTable
;

I get this:

Msg 15151, Level 16, State 1, Line 1

Cannot find the object ‘MyTable’, because it does not exist or you do not have permission.

I know it’s there; I just created it. What’s wrong?

The problem is that this isn’t an object per se, but a type. As a result, to move a type, I need to use a different syntax:

ALTER SCHEMA NewSchema TRANSFER type::OldSchema.MyTable
;
GO

That works fine and the type has moved. The class attribute of the notation is

CLASS::Schema.Object

I haven’t found good documentation of this, but there are numerous examples in BOL that show this is how you address various “types” in SQL Server.

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.