-1

Let's say for example I've got the following two types:

CREATE TYPE TBL1 AS TABLE
(   id int,
    name varchar(max),
    status_id int )
GO

CREATE TYPE TBL2 AS TABLE
(   
    id int,
    status varchar(max)
)
GO

These are used with the following piece of SQL

DECLARE @T1 AS TBL1
INSERT INTO @T1 (id, name, status_id)
    VALUES  (1, 'Test1', 1),
            (2, 'Test2', 2)

DECLARE @T2 AS TBL2
INSERT INTO @T2 (id, status)
    VALUES  (1, 'New'),
            (2, 'Old')

Now if I want to use these Types in a query I get an error:

SELECT * 
FROM @T1 
INNER JOIN @T2 ON @T1.status_id = @T2.id

The error is

Must declare the scalar variable '@T1'

which is strange because I defined the variable as a type.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SouthL
  • 175
  • 2
  • 12

1 Answers1

1

It seems that you can only use the variable @T1 and @T2 in the FROM-clause. If you give it an alias you can make use of its columns in any other clause.

For example, the following query will work:

SELECT * 
    FROM @T1 as t1 
    INNER JOIN @T2 as t2 ON t1.status_id = t2.id
SouthL
  • 175
  • 2
  • 12