Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: temp table error

    hi,
    I have the following code:
    ....
    .....
    set @sql='create table #choice (cid int identity(1,1),Uid int,'+@c1+' int,'+@c2+' int,'+@c3+' int)'
    print @sql
    exec sp_executesql @sql
    set @insert ='insert into #choice (uid,'+convert(varchar(10),@c1)+')
    select uid ,oid from answermaster where qid='+convert(varchar(10),@c1)
    print @insert
    exec sp_executesql @insert
    select * from #choice

    ....

    it gives me an error as:
    Invalid object name '#choice'.

    any views?
    thankyou.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Its all a matter of scope of the Temptable

    set @sql='create table #choice (cid int identity(1,1),Uid int,'+@c1+' int,'+@c2+' int,'+@c3+' int)'
    print @sql
    exec sp_executesql @sql
    Here you have created the table but its scope is lost as soon as the sp_executesql completes execution
    set @insert ='insert into #choice (uid,'+convert(varchar(10),@c1)+')
    select uid ,oid from answermaster where qid='+convert(varchar(10),@c1)
    print @insert
    exec sp_executesql @insert
    select * from #choice
    As such since the table does not exist outside the scope .. you will not be able to query it

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by Enigma
    Its all a matter of scope of the Temptable



    Here you have created the table but its scope is lost as soon as the sp_executesql completes execution


    As such since the table does not exist outside the scope .. you will not be able to query it
    thnks

Posting Permissions

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