Post reply

Regarding the usage of Temporary table

  • March 12, 2012 at 4:42 am

    #391775

    Hi All,

    Can any one tell me whats wrong with the following code?

    Declare @tbl_temp Table(ApplicationId int,ACPId int)

    Declare @sqlquery varchar(max)

    insert into @tbl_temp values(444,12215)

    insert into @tbl_temp values(444,13801)

    set @sqlquery='select * from Invoice I inner join @tbl_Temp T on I.ApplicaitonId=T.ApplicationID'

    exec(@SqlQuery)

    Its giving an error saying 'Must declare the table variable "@tbl_Temp". How to join the temporary table? Please advice..

  • March 12, 2012 at 4:55 am

    #1457736

    The table variable has no visibility within the dynamic SQL, it's only visible in the scope that it's declared in.

    That said, there is absolutely no need for dynamic SQL in this case.

    Declare @tbl_temp Table(ApplicationId int,ACPId int)

    insert into @tbl_temp values(444,12215)

    insert into @tbl_temp values(444,13801)

    select * from Invoice I inner join @tbl_Temp T on I.ApplicaitonId=T.ApplicationID

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2012 at 5:02 am

    #1457739

    I have posted the Sample Code there.In the Original requirement the SQL statement is dynamically generated. It works if i do this way but it will be a recoding again for me as I have to use the same temporary table many times in the entire code.

    Declare @sqlquery varchar(max)

    set @sqlquery='Declare @tbl_temp Table(ApplicationId int,ACPId int)

    insert into @tbl_temp values(444,12215)

    insert into @tbl_temp values(444,13801)

    select * from @tbl_temp'

    exec(@SqlQuery)

    Please let me know if there are any other alternatives.

  • March 12, 2012 at 5:12 am

    #1457742

    instead of executing join query using variable..directly place the join statement after insert statements..this will work

  • March 12, 2012 at 5:25 am

    #1457746

    If you are dynamically generating the sql why the need for a temporary table? or the exec statement?

    The only way you can make a temporary table globally visible (not table varible that you declared) is to use create table ##tablename(column int)

    This is potentially dangerous as multiple processes will have visibility of this table and may not work for you as it'll be consistant throughout you applications.

    I think we need more information on why you've gone down this route in design as at the moment it could be that it will not work. At least not very efficiently.

  • March 12, 2012 at 5:29 am

    #1457748

    Use a temp table instead of a table variable, that way it will be visible within the scope of the dynamic SQL (which I still see no reason for)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2012 at 5:29 am

    #1457749

    Each dynamic SQL Statement that gets generated has to be joined with the Temporary table.

  • March 12, 2012 at 5:30 am

    #1457751

    MysteryJimbo (3/12/2012)

    The only way you can make a temporary table globally visible (not table varible that you declared) is to use create table ##tablename(column int)

    Errr, no need for global temp tables here. Normal temp tables (CREATE TABLE #temp) are visible in the scope they are created in and any lower scopes, so create a temp table in the outer scope and it will be visible within the dynamic SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2012 at 5:31 am

    #1457755

    If you absolutely need the dynamic SQL, this will work.

    CREATE TABLE #tbl_temp (ApplicationId int,ACPId int)

    Declare @sqlquery varchar(max)

    insert into #tbl_temp values(444,12215)

    insert into #tbl_temp values(444,13801)

    set @sqlquery='select * from Invoice I inner join #tbl_Temp T on I.ApplicaitonId=T.ApplicationID'

    exec(@SqlQuery)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2012 at 5:58 am

    #1457767

    Thank you Gila..It worked for me.but just want to know Is there any disadvantage in using local temporary table?

  • March 12, 2012 at 6:07 am

    #1457773

    You can try this..but I'm afraid this can be standard soln...go for ##table

    Declare @sqlquery varchar(max)

    Set @sqlquery='declare @tbl_temp table(applicationid int, avoid int)'

    Set @sqlquery=@sqlquery+'insert into @tbl_temp values(444,12215) '

    Set @sqlquey=@sqlquery+'insert into @tbl_temp values(444,1380)'

    Set @sqlquery=@sqlquery+'select a.applicationid,a.acpid @tbl_temp a join invoice b on a.applicationid=b.applicationid'

    Exec(@sqlquery)

  • March 12, 2012 at 6:55 am

    #1457805

    chandrika.r 91171 (3/12/2012)

    Thank you Gila..It worked for me.but just want to know Is there any disadvantage in using local temporary table?

    No, it's almost the same as a table variable, may even perform better.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2012 at 6:55 am

    #1457806

    gugan_ta (3/12/2012)

    You can try this..but I'm afraid this can be standard soln...go for ##table

    There is no need for a global temp table here.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2012 at 10:57 pm

    #1458292

    GilaMonster (3/12/2012)

    gugan_ta (3/12/2012)

    You can try this..but I'm afraid this can be standard soln...go for ##table

    There is no need for a global temp table here.

    To add to what Gail said, if done incorrectly, the use of a global temp table can make concurrent runs impossible especially if the two runs are different and have different column requirements.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply