Edwin Edwin - 7 months ago 46
SQL Question

Must declare the scalar variable using internal tables

For some reason, I get the mentioned error message while using two internal tables, declared at the beginning of the script.
I can insert data into the tables, but when I try to join them on a field, present in both tables it wount let me.
I am working in SQL2012

SELECT *
FROM @ITAB01
JOIN @ITAB02
on @ITAB01.country=@ITAB02.country



Error: Must declare the scalar variable "@ITAB01".. Error 137. SQLSTATE 42000. Severity 15. MsgState 2. Line 83.

Answer

You need to give a name to the table variable.

NOT LIKE THIS:

declare @t1 table
(
    p1 int
)

declare @t2 table
(
    p2 int
)

select *
from @t1,
@t2
where @t1.p1 = @t2.p2

BUT LIKE THIS:

declare @t1 table
(
    p1 int
)

declare @t2 table
(
    p2 int
)

select *
from @t1 t1,
@t2 t2
where t1.p1 = t2.p2

So in your case:

SELECT * 
FROM @ITAB01 ITAB01
JOIN @ITAB02 ITAB02
on ITAB01.country=ITAB02.country