CODEWITHSUNDEEP
sql-serverjoin
Alex Lemesios
Alex Lemesios

Reputation: 532

SQL Server: joining a declared table variable with local server tables

I have declared a table within a stored procedure i.e : 

DECLARE @fooTable table ( idfoo INT)

INSERT INTO @fooTable
  EXEC getBOO 

SELECT * FROM @fooTable

I want to join this table with other tables from my local server db i.e : 

SELECT *
FROM tbl1 
RIGHT JOIN tbl2 ON tbl1.foofield = tbl2.foofield
RIGHT JOIN @fooTable ON tbl2.foofield = @fooTable.foofield ;

I get an error:

Must declare the scalar variable "@fooTable"

I've searched a bit for this error, most of the posts I found stated that the table I'm trying to reach is out of scope.

Well the declaration of @fooTable, as well as the JOIN query, are a part of the same stored procedure, so i can't find any scoping issues.

What is the best practise to JOIN a variable table (@fooTable) with tables from my local server ? (tbl1, tbl2) ?

Upvotes: 1

Views: 4212

Answers (4)

onedaywhen
onedaywhen

Reputation: 57053

The issue here is a relation operation (join) on two tables with columns having the same name and type. In 1992, this problem was solved when new join syntax was introduced (NATURAL, USING, etc). Your query could be fixed in this way:

SELECT * 
  FROM tbl1 
      NATURAL RIGHT JOIN tbl2
      NATURAL RIGHT JOIN @fooTable;

However, SQL Server has not adopted the 1992 join types so we are stuck with SQL's original resolution, dating back to the 1970s: range variables. [Others here are using the inappropriate term 'aliases' for the same concept.] To be clear: the user is required to use range variables to resolve the 'names in common' issue.

Though you may not realize it, you are using range variables in your code e.g. in the line

ON tbl1.foofield = tbl2.foofield

both tbl1 and tbl2 are range variables: each represents the row of the table which it ranges over. Because you didn't specify your own range variable names, the name of the table is used (again this has its roots in 1970s SQL) i.e. is equivalent to:

SELECT *
 FROM tbl1 tbl1
      RIGHT JOIN tbl2 tbl2
         ON tbl1.foofield = tbl2.foofield

I guess where your code is falling down is that @fooTable is not a valid range variable name i.e. 

  RIGHT JOIN @fooTable @fooTable

Therefore, the fix is to pick a range variable name that makes SQL Server happy e.g.

SELECT *
  FROM tbl2 t2
       RIGHT JOIN @fooTable f
          ON t2.foofield  = f.foofield

My preferred solution is for SQL Server to implement the 1992 join types! Not within my control but I have voted for it here.

Upvotes: 1

brahmareddy
brahmareddy

Reputation: 121

DECLARE @fooTable table ( idfoo INT)
INSERT INTO @fooTable
EXEC getBOO 
SELECT * FROM @fooTable

SELECT *
FROM tbl1 RIGHT JOIN tbl2
ON tbl1.foofield = tbl2.foofield
RIGHT JOIN @fooTable f2
ON tbl2.foofield = f2.idfoo ;

Upvotes: 4

Peter B
Peter B

Reputation: 24222

You need to provide an alias for the table variable:

SELECT *
FROM tbl1 RIGHT JOIN tbl2
ON tbl1.foofield = tbl2.foofield
RIGHT JOIN @fooTable FooTable
ON tbl2.foofield = FooTable.foofield;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

Just give a Alias name to table variable and use it for reference 

SELECT *
FROM   tbl1
       RIGHT JOIN tbl2
               ON tbl1.foofield = tbl2.foofield
       RIGHT JOIN @fooTable FT -- Here 
               ON tbl2.foofield = FT.foofield; 
                                --^^-- Here

Upvotes: 3

Related Questions