sql - There is already an object named '#result' in the database

Question

Alter Procedure sp_Member(
  @comcode int = 0,
  @SubComCode int = 0
)
as begin
  set nocount on
  If @comcode='0'
  begin
    select (
      select sum(amount)
        from tbcoudet
        where memcode=tbm.memcode and
              expyear=(select max(expyear) from tbexpyear)
              and exists (
                select itemcode
                from tbitem
                where comcode=@comcode and
                  SubComCode=@SubComCode and
                  itemcode=tbcoudet.itemcode
              )
        group by memcode,expyear
      )'TurnOver', *
    into #result from tbmember tbm where can_flag='N'
  end
  If @subcomcode='0'
  begin
    select (
      select sum(amount)
      from tbcoudet
      where memcode=tbm.memcode and expyear=(select max(expyear) from tbexpyear)
        and exists (
          select itemcode
          from tbitem
          where comcode=@comcode and
            itemcode=tbcoudet.itemcode
        )
      group by memcode,expyear
    )'TurnOver', *
    into #result from tbmember tbm where can_flag='N'
  end

  select top 10 * from #result where TurnOver is not null order by TurnOver desc
end

That is my sql code and when i am going to execute store procedure then I get the error

There is already an object named '#result' in the database.

Can anyone tell me what the problem is?

Answer 1

The error is: there is already a temporary table by that name - don't re-create it if it already exists....

The problem lies in the way you do your select's - you have two places where you have

select (columns)
into #result 
from tbmember tbm 
...

The first time around, this will create the temporary table #result. And the second time around, you'll get the error - since it cannot create a table that already exists.

So you need to change your code to:

• explicitly create the table #result in the beginning

  CREATE TABLE #result ( ...give list of columns and their datatypes here .....)
  

• use code like this to insert values:

  INSERT INTO #result(colum list)
     SELECT (column list) 
     FROM  .......
     WHERE .......
  

That code will work and you will be able to insert two sets of data into your temporary table.