Results 1 to 3 of 3

Thread: Table Variables

  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: Table Variables

    Is it possible to declare a table variable witha composite primary key? IE to replace #MyTable with @MyTable

    -- #MyTable
    create table #MyTable
    (
    MstID int not null
    ,DtlID int not null
    ,Property1 varchar(100)
    ,Property2 varchar(100)
    )
    alter table #MyTable ADD
    constraint [PK_Example] primary key
    (
    [MstID],
    [DtlID]
    )
    -- with @MyTable
    declare @MyTable table
    (
    MstID int not null
    ,DtlID int not null
    ,Property1 varchar(100)
    ,Property2 varchar(100)
    )
    -- Here alter table will not accept a table variable.

  2. #2
    Join Date
    May 2003
    Posts
    12
    Try this.

    declare @MyTable Table
    (
    MstID int not null,
    DtlID int not null,
    Property1 varchar(100),
    Property2 varchar(100),
    primary key
    (
    [MstID],
    [DtlID]
    ))

  3. #3
    Join Date
    May 2003
    Posts
    4

    Correlated Subquery

    I am not been able to reference a table variable by name in a correlated subquery. Does anyone know why? The outer table's columns are still in scope, but it won't recognize the name.

    Example

    ---------This works:
    declare @t1 table
    (
    EntryID int primary key
    ,Property1 varchar(100)
    )
    insert into @t1 values(1,'world.')

    declare @t2 table
    (
    EntryID_2 int primary key
    ,Property1 varchar(100)
    ,Property2 varchar(100)
    )
    insert into @t2 (EntryID_2, Property1)
    values (1,'Hello ')

    update @t2
    set Property2 = (
    select Property1 from @t1 t1
    where t1.EntryID = EntryID_2
    )
    select * from @t2

    -----This fails with error "Must declare the variable '@t2'":

    declare @t1 table
    (
    EntryID int primary key
    ,Property1 varchar(100)
    )
    insert into @t1 values(1,'world.')

    declare @t2 table
    (
    EntryID int primary key
    ,Property1 varchar(100)
    ,Property2 varchar(100)
    )
    insert into @t2 (EntryID, Property1)
    values (1,'Hello ')

    update @t2
    set Property2 = (
    select Property1 from @t1
    where EntryID = @t2.EntryID
    )
    select * from @t2

    /*
    This seems to be a problem with addressing in general, as aliased table variables are accepted.
    bad:
    select Property1 from @t1
    where @t1.EntryID = 1
    good:
    select Property1 from @t1 t1
    where t1.EntryID = 1
    Unfortuantely, the only way I know to alias an updated table is with a from statement
    eg:
    update @t2
    set Property2 = t1.Property1
    from @t2 t2
    inner join @t1 t1
    on t1.EntryID = t2.EntryID
    -- which works fine but reads awwkwardly in addition to precluding aggregates and other aperations not allowed in the set list of an update statement.
    */

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •