none
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

     

     

    Tuesday, July 24, 2007 2:38 PM

Answers

  • 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

     

     

     

     

    Tuesday, July 24, 2007 3:07 PM
  • 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

    Tuesday, July 24, 2007 3:58 PM

All replies

  • 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 

     

    Tuesday, July 24, 2007 2:53 PM
  • 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.

     

    Tuesday, July 24, 2007 2:57 PM
  • 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'.

    Tuesday, July 24, 2007 3:07 PM
  • 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

     

     

     

     

    Tuesday, July 24, 2007 3:07 PM
  • 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'.

    Tuesday, July 24, 2007 3:38 PM
  • 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

    Tuesday, July 24, 2007 3:58 PM
  • Thank you all. I have managed to complete my development now.
    Wednesday, July 25, 2007 7:52 PM