Table variables problem /wonky join 2

[gdrenfrew]

Hello,

I'm getting "Error 137: Must declare the variable @TableVar" and don't understand why.

I'm using a table variable in a stored procedure so I don't have the overhead of temporary tables.

I pass in a ClientID to the stored procedure, then return results dependant on what the clientID was. Once I populate my table variable, I can't then select from it again under certain circumstances. The error only appears in the second ELSE's WHERE statement. If I comment that bit out, it works alright.

This is abbreviated for understandability but is the gist of my procedure.

declare @TableVar as table
(idfield int,
descn varchar(50)

insert into @tablevar
select id, descn
from t_products

if @ClientID = -1
BEGIN
Select * from @TableVAR
END
ELSE
BEGIN
-- ** Error seems to be caused by the where clause **
Select * from @TableVar, t_orderitems
WHERE @TableVAR.idfield = t_orderitems.idfield
END

Any ideas chaps/chappesses? NB, the table is being populated ok as I can select the contents out before i do the If @ClientID bit.
thanks
Graeme

 

[aroggeband]

Correct the offending line as follows:

-- ** Error seems to be caused by the where clause **
Select * from @TableVar TV, t_orderitems
WHERE TV.idfield = t_orderitems.idfield

also remove the 'as' from the table declaration, that seems to fail for me:
declare @TableVar table
(idfield int,
descn varchar(50)

 

[gdrenfrew]

Great stuff, thank you.
Graeme