table data type

Question

I am looking at replacing tempory tables with table data type, but I cannot workout how to join a permanent table with the 'table' data type.

 

select * from @a inner join B where B.id = @a.B_id

 

 

Answer 1

Now a days, its very common question in this forum, and answered well on all the post.

You can’t specify the table as variable. But you can use the dynamic SQL to fetch the data from the variable which hold the table name.

 

 

Exec ('select * from ' + @a + ' as Main inner join B where B.id = Main.B_id')

 

--or

 

Declare @SQL as NVarchar(2000);

Set @SQL = N'select * from ' + @a + ' as Main inner join B where B.id = Main.B_id'

Exec sp_executesql @SQL 

 

Answer 2

I meant:

 

declare @Table table (a int, b varchar(30))

 

insert into @Table (a,b) values (1,'a')

insert into @Table (a,b) values (2,'b')

 

select * from @Table

 

select * from RealTable  inner join @Table on @Table.a = RealTable .ID

I get the error      Must declare the variable '@Table'

 

If this is a temporty table, not a 'table' variable then it works.

 

Answer 3

I suspect that you still have the word 'GO' after the DECLARE @Table.

 

Using 'GO' causes a new batch, and any previously defined variables fall out of scope. If it exists, remove the word 'GO'.

Answer 4

Ohh.. I misunderstood…

Here the solution, use the alias name to fix your issue.

 

Code Snippet

declare @Table table (a int, b varchar(30))

 

insert into @Table (a,b) values (1,'a')

insert into @Table (a,b) values (2,'b')

 

select * from @Table

 

select * from RealTable  inner join @Table as MyTable on MyTable.a = RealTable.ID

 

 

 

 

Answer 5

As Mani indicates, you MUST alias a table variable when used in the ON condition, WHERE clause, GROUP BY, etc.

 

Mani choose 'MyTable' as the alias name -you can choose anything legitimate name, including 't' for the table variable and 'r' for the 'RealTable'.

Answer 6

Hi,

 

You can use an alias for your table variable or you can quoted it to let SS know that it is an identifier.

 

declare @t1 table(c1 int)

declare @t2 table(c1 int)

 

insert into @t1 values(1)

insert into @t2 values(1)

 

select *

from @t1 as t1 inner join @t2 as t2 on t1.c1 = t2.c1

 

select *

from @t1 inner join @t2 on [@t1].c1 = [@t2].c1

 

set quoted_identifier on

 

select *

from @t1 inner join @t2 on "@t1".c1 = "@t2".c1

 

set quoted_identifier off

go

 

 

AMB

Answer 7

Thank you all. I have managed to complete my development now.