none
T-SQL Same Temp Table in IF Else Block Error

    Question

  • Hi Friends,

    I would like to use same Temp Table in Multiple IF Else Block of Store Proc,

    But, when I have compiled this Store Proc, It was giving me error that #Temp is already exist.

    But actually, Only One block out of all If Else Balock will excute, even though ,it giving me compiation error.

    Server: Msg 2714, Level 16, State 1, Procedure test, Line 9
    There is already an object named '#temp1' in the database.

    Can anyone suggest me solution of this problem ?

     

    Thanks.

    Saturday, October 15, 2011 9:08 PM

Answers

  • Hi

    crate the #temp table before the IF Else block, then use the table inside the if..else bloack

     

    create table #temp(id int,name char(10))

    if (1=1)

    begin

    insert into #temp
    select id,name from atable 

    end

    else
    begn

    insert into #temp

    select id,name from atable 

    end 

     

    hope this help..

    VT

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Proposed as answer by Naomi NModerator Sunday, October 16, 2011 2:27 AM
    • Marked as answer by KJian_ Monday, October 24, 2011 6:04 AM
    Saturday, October 15, 2011 9:38 PM

All replies

  • Hi

    crate the #temp table before the IF Else block, then use the table inside the if..else bloack

     

    create table #temp(id int,name char(10))

    if (1=1)

    begin

    insert into #temp
    select id,name from atable 

    end

    else
    begn

    insert into #temp

    select id,name from atable 

    end 

     

    hope this help..

    VT

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Proposed as answer by Naomi NModerator Sunday, October 16, 2011 2:27 AM
    • Marked as answer by KJian_ Monday, October 24, 2011 6:04 AM
    Saturday, October 15, 2011 9:38 PM
  • SQL is raising this error because you are still in same session.
    You can't create same temp table in current running session.
    Shatrughna.
    Sunday, October 16, 2011 1:22 AM