Using a table variable in SQL Server

Question

I have designed a small query for table variable. This is the code:

DECLARE @TESTTABLE TABLE (ID INT, NAME VARCHAR(40))

INSERT INTO @TESTTABLE 
VALUES (1, 'WALLEY'), (2, 'BARRY'), (3, 'TIM'),
       (4, 'DICK'), (5, 'BRUCE')

My question is SQL allows use of alias for table variable like in the below query.

SELECT * 
FROM @TESTTABLE t 
INNER JOIN EMPLOYEE_DETAILS ON t.ID = BusinessEntityID

But you cannot use the actual table variable name like below:

SELECT * 
FROM @TESTTABLE 
INNER JOIN EMPLOYEE_DETAILS ON @TESTTABLE.ID = BusinessEntityID

The above query shows an error.

Does anyone have an answer for this? Would be really helpful.

I've fixed your code. FYI, the way to show code is to put a blank line, and then indent each line of code four spaces. If you edit your post, you'll see what i've done. — BobRodes
– BobRodes, Commented Sep 5, 2018 at 16:59
What error are you seeing when you run the code? Instinctively I'd suggest that you use aliases and see if it helps, something like SELECT * FROM @TESTTABLE tt INNER JOIN EMPLOYEE_DETAILS emp_d ON tt.ID = emp_d.BusinessEntityID — Dessma
– Dessma, Commented Sep 5, 2018 at 17:00
Hi, your question isn't very clear. As well as including the details of the DBMS and error message, could you clarify what you actually want to know? You seem to already know a way to make the query work (use an alias). Are you looking for something shorter? Or do you just want to understand why it doesn't work? — IMSoP
– IMSoP, Commented Sep 5, 2018 at 17:02
In any case, I think it's mostly universal that database objects (like tables) have to be static. So if you want it parametrized you need dynamic SQL. — Error_2646
– Error_2646, Commented Sep 5, 2018 at 17:02

Dessma · Accepted Answer · 2018-09-05 17:07:20Z

5

In T-SQL, you need to use aliases when joining variable tables.

Reference : https://odetocode.com/articles/365.aspx (see section : Restrictions)

It should give you something like this :

SELECT * 
FROM @TESTTABLE tt 
INNER JOIN EMPLOYEE_DETAILS emp_d 
ON tt.ID = emp_d.BusinessEntityID

answered Sep 5, 2018 at 17:07

Dessma

6093 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

RBarryYoung Over a year ago

This does not appear to be true. Do you have a Microsoft reference?

Gareth Lyons Over a year ago

From MS Docs "Outside a FROM clause, table variables must be referenced by using an alias"

RBarryYoung Over a year ago

@GarethLyons But this is in a FROM clause.

RBarryYoung Over a year ago

@GarethLyons Also, I've tested my solution outside of the FROM clause and it still works. Either there's a bug in the SQL Server parser or the Doc is wrong.

Dessma Over a year ago

@RBarryYoung from what I can tell from the original post, it works when it is in the FROM clause, but not in the JOIN / ON. That's also what the documentation suggests. Can't quite test it myself at the moment though.

|

RBarryYoung · Accepted Answer · 2018-09-05 17:15:00Z

2

Use "[..]" to quote the table variable's name. This works for me:

SELECT * 
FROM @TESTTABLE INNER JOIN 
     EMPLOYEE_DETAILS
     ON [@TESTTABLE].ID = BusinessEntityID

That said, I would probably prefer to use an alias, or just use a CTE and forego the table variable altogether.

edited Sep 5, 2018 at 17:15

answered Sep 5, 2018 at 17:08

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

1 Comment

Tushal Singh Over a year ago

Thnx for the reply. But his is not working. I am getting the below error. The multi-part identifier "@TESTTABLE.ID" could not be bound.

Collectives™ on Stack Overflow

Using a table variable in SQL Server

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related