Solved

Why can't I JOIN to a @TempTable ?

Posted on 2008-10-13
6
1,013 Views
Last Modified: 2012-05-05
I have written the following T-SQL, which works correctly up to the last line.  I'm using SQL Server 2000.

After filling the @TempTable I can't join it to a "real" table.  The error message returned is "Must declare the variable '@TempTable'."

What really confuses me is that if I modify the last line to read "SELECT * FROM @TempTable" it works without problem.  Seems like the join is the problem.

Thanks.


DECLARE @PN varchar(15), 
@Hours Varchar(15)
 
DECLARE @TempTable TABLE(ItemNumber Varchar(15), Hours Varchar(15))
 
DECLARE  CursorTemplate CURSOR 
FAST_FORWARD FOR       
      SELECT DISTINCT Item FROM Tedia.dbo.FSER_Invoice 
 
OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate 
INTO      @Pn
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	
    INSERT INTO @TempTable
 
    SELECT DISTINCT
	TopLevelItemNumber, Sum(RequiredQuantity / ParentItemLotSizeQuantity) AS Labor
 
	FROM FSDBB1.dbo.FSE_MultiLevelBill(@PN)
 
	WHERE ComponentType = 'R'
 
	GROUP BY TopLevelItemNumber
	
 
    FETCH NEXT FROM CursorTemplate 
    INTO @PN
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate
 
--***** NEXT LINE IS PROBLEM *****
 
SELECT * FROM Tedia.dbo.FSER_Invoice JOIN @TempTable ON Tedia.dbo.FSER_Invoice.Item = @TempTable.ItemNumber

Open in new window

0
Comment
Question by:SteveB2175
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:CCongdon
ID: 22702382
Are you sending an entire table across as a 'variable'?
Also, maybe I missed something, but when I've created temp tables, I've always prefaced them with # not @. And I used CREATE TABLE not DECLARE.
0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 400 total points
ID: 22702412
u need to alias your variable table like this

SELECT
*
FROM Tedia.dbo.FSER_Invoice
JOIN @TempTable tt
      ON Tedia.dbo.FSER_Invoice.Item = tt.ItemNumber

and it should work
0
 
LVL 9

Assisted Solution

by:CCongdon
CCongdon earned 100 total points
ID: 22702422
OK, I see. I just looked it up. I understand now. You need to declare an alias for the table in the select statement. For example, this gets the same error you showed:
SELECT id
    FROM @foo
    INNER JOIN #foo
    ON @foo.id = #foo.id

 
But, this works:
SELECT id
    FROM @foo f
    INNER JOIN #foo
    ON f.id = #foo.id
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 5

Expert Comment

by:Cvijo123
ID: 22702435

@CCongdon: when you use @ that means it is variable table (memory table) and when you used # that means table is saved on disk.

makeing @ tables you use Declare @tableName table statment and for # tables you use Create table #tableName
0
 

Author Comment

by:SteveB2175
ID: 22702506
That worked.  I never would have found it on my own.  I don't understand the underlying logic that requires an alias, but I'll go back and look for that.

Thanks for your help.
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 22702547
@Cvijo123: Yeah, I learned something new today. I found a site the explained the difference between a declared table and a temp table (such as being able to use a declared table inside a function). The declared table would appear to be SUPER temporary and doesn't even hit the transaction log amongst other things.
 
@SteveB2175:
Here's the site I got my info from. It also shows some of the limitations of the table variable.
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
And the correct term for an @Table is a 'Table Variable' so I was sort of right :) A temp table is created with #....
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 68
What is the proper way to use for criteria in left join? 7 33
Query group by data in SQL Server - cursor? 3 50
SQL Recursion schedule 13 19
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question