Must declare the scalar variable

Question

First I have created an in-memory table with one column and using those column values I have an inner join with another table. While doing so I am getting this error:

Must declare the scalar variable @Temporary.

Can any one explain where I am going wrong?

DECLARE @ID INT
Declare @Temporary Table
(
AccountID INT
)  

DECLARE cur CURSOR FOR

SELECT DISTINCT ParentItem from ItemBillOfMaterial

OPEN cur

FETCH NEXT FROM cur INTO @ID;

WHILE @@FETCH_STATUS = 0

BEGIN 

Insert into @Temporary Values(@ID)  

FETCH NEXT FROM cur INTO @ID;

END

SELECT UOM FROM Item

INNER JOIN @Temporary

ON [email protected]

CLOSE cur;

DEALLOCATE cur;

Answer 1

You have to use an alias when referencing the table in your join condition

SELECT UOM FROM Item
INNER JOIN @Temporary t
ON Item.ItemID=t.AccountID

Whilst that fixes the problem you're having, you don't need the temporary table, or the cursor. This query could be rewritten as:

SELECT UOM 
FROM Item
WHERE ItemID IN (SELECT DISTINCT ParentItem FROM ItemBillOfMaterial)