D.Truitt D.Truitt - 8 months ago 42
SQL Question

Why am I getting an error message that says "Invalid Object Name" when I've declared my variable in the current batch?

declare @docs table(
DocID int

insert into @docs (DocID)
select DocID
from tblDoc
where tblDoc.BatchID = #Batch_id#

select *
from tblPage
inner join [@docs]
on tblPage.DocID = [@docs].DocID

I've tried wrapping the @docs in square brackets, I've tried removing the where clause, I've tried different permutations of the insert command, all without success.

I keep getting either the

must declare scalar variable @docs" message, or the "invalid object name message.

Am I missing something obvious here?

Using Microsoft SQL Server 2008 (RTM) - 10.0.1600.22

Answer Source

Alias your table var

select *
from tblPage
inner join @docs d
on tblPage.DocID = d.DocID

just don't ask me why :)