T-SQL: How to join @variable tables (another try)

Question

I know, this is a duplicate question of this question. But i encountered the same problem and maybe i can provide some more available informations.

If i try out the following example query:

DECLARE @_Files TABLE (ID INT, Filename VARCHAR(MAX));
DECLARE @_Errors TABLE (ID INT, Label VARCHAR(MAX), Value VARCHAR(MAX));

insert into @_Files
    select 73, 'abc'

insert into @_Errors
    select 73, 'Some label name', 'Just a value'

select
    *
from
    @_Errors 
    inner join @_Files
    on @_Errors.ID = @_Files.ID

I got the error message:

The @_Errors-scalar variable must be declared.
The @_Files-scalar variable must be declared.

I'm using SQL-Server 2008 R2 Express and it is running at compatibility level 100. So any ideas why i get this error?

Martin Smith · Accepted Answer · 2012-12-10T14:30:35.157

14

Either give them an alias that you then refer to in the JOIN or use square brackets. The below mixes both possibilities.

SELECT *
FROM   @_Errors Errors
       INNER JOIN @_Files 
         ON Errors.ID  = [@_Files].ID

There is a Connect Item discussing this issue here

edited Dec 10 '12 at 14:30

answered Dec 10 '12 at 14:18

Martin Smith

438,706
87
741
845

Also there is no `File_ID` in the `@_Errors` table. [**demo**](http://www.sqlfiddle.com/#!3/d41d8/6999) – Mahmoud Gamal Dec 10 '12 at 14:20
@MahmoudGamal - Agreed. Without more info from the OP not sure how to resolve that though. They will need to make sure that they change the column name in the table variable or the query. – Martin Smith Dec 10 '12 at 14:21
@MahmoudGamal: All names are fixed. – Oliver Dec 10 '12 at 14:26

score 1 · Answer 2 · answered Dec 10 '12 at 14:30

This is because the INNER JOIN expects the parameters are variables. This should work:

DECLARE @_FileIDs TABLE (ID INT, Filename VARCHAR(MAX));
DECLARE @_Errors TABLE (ID INT, Label VARCHAR(MAX), Value VARCHAR(MAX));

insert into @_FileIDs
    select 73, 'abc'

insert into @_Errors
    select 73, 'Some label name', 'Just a value'

select
    *
from
    @_Errors AS tblErrors
    inner join @_FileIDs AS tblFileID
    on tblErrors.ID = tblFileID.ID

T-SQL: How to join @variable tables (another try)

2 Answers2

Linked

Related