Something I learnt about SQL table type this week

Friday, December 10, 2004

Q: When I try to compile the procedure below I get the error Must declare the variable '@TableType'. Can you guess why the following stored procedure does not compile?

CREATE PROCEDURE #SystemTablesAndColumns
AS
DECLARE @TableType TABLE (id int, name sysname)

INSERT INTO @TableType
(
 id,
 name
)
SELECT id,
       name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

SELECT @TableType.id AS TableID,
       @TableType.name AS TableName,
       syscolumns.colid AS ColumnID,
       syscolumns.name AS ColumnName
FROM @TableType
  INNER JOIN
     syscolumns ON @TableType.id = syscolumns.id
ORDER BY @TableType.name,
         syscolumns.name

RETURN

A: Because aliases are missing for the table type, as can be noted below:

CREATE PROCEDURE #SystemTablesAndColumns
AS
DECLARE @TableType TABLE (id int, name sysname)

INSERT INTO @TableType
(
 id,
 name
)
SELECT id,
       name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

SELECT TableType.id AS TableID,
       TableType.name AS TableName,
       syscolumns.colid AS ColumnID,
       syscolumns.name AS ColumnName
FROM @TableType AS TableType
  INNER JOIN
     syscolumns ON TableType.id = syscolumns.id
ORDER BY TableType.name,
         syscolumns.name

RETURN

P.S.: What the temporary stored procedure above does can be acomplished in other ways. I just used it that way to show up the problem I faced this week and how I workarounded it.

No Comments