Geek asks and answers

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

I know this is a duplicate question this question . But I came across the same problem and maybe I can provide more accessible information.

If I try the following query example:

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 an error message:

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

I am using SQL-Server 2008 R2 Express and running at compatibility level 100. So any ideas why I am getting this error?

+2
source share
2 answers

Either give them an alias that you then call in JOIN, or use square brackets. The following mixes both possibilities.

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

There is a connection point here discussing this issue

+11
source

, INNER JOIN , . :

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
+1

More articles:


All Articles