sql - 数据库中已经有一个名为 '#result' 的对象

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

那是我的 sql 代码，当我要执行存储过程时，我收到错误

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

谁能告诉我是什么问题？

Answer 1

错误是:已经有一个同名的临时表 - 如果它已经存在，请不要重新创建它....

问题在于您进行选择的方式 - 您有两个地方

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

第一次，这将 创建 临时表 #result .第二次，你会得到错误 - 因为它不能创建一个已经存在的表。

因此，您需要将代码更改为:

显式创建表 #result在一开始的时候

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

使用这样的代码插入值:

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

该代码将起作用，您将能够将两组数据插入到临时表中。