数据库中已经有一个名为“#result"的对象 [英] There is already an object named '#result' in the database

问题描述

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代码，当我要执行存储过程时，我收到错误

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?

推荐答案

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

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 
...

第一次，这将创建临时表#result.第二次，您将收到错误消息 - 因为它无法创建已存在的表.

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:

• 在开头显式创建表#result

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.

这篇关于数据库中已经有一个名为“#result"的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！