可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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?

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

回答2:

If you are not sure whether temp table exists or not but you are sure that before next statement you won't need it use object_id function this way:

if object_id('tempdb..#result', 'u') is not null
    drop table #result

It will not cause an error if table doesn't exist and drop it if it does. In my opinion it's a good practice to use just before each:

select ... into #temp from ...

回答3:

SELECT ... INTO ... always wants to create a new temp table.

Instead, create the temp table at the top of the procedure (using create table #result (columns)), and then re-write your selects using INSERT INTO (columns) SELECT ... instead.

I'm unable to write out a full example, since I don't know your column definitions.

Even if, as a matter of course, only one of your if statements is true, the T-SQL parser is rather simple, and still "sees" both attempted declarations of #result.

As an aside, I'm not sure why you're writing your if statements like this:

if @subcomcode='0'

Since @subcomcode is an int, you'd be better off not quoting the 0 literal.

回答4:

Agreed it's good to drop before creating new temp tables. But a simple workaround is just to close your DB connection when debugging this type of stuff. As the disconnection - wipes all those for you.

回答5:

I have similar problem, but neither proposed solution here helps me. I have 6 ifs similar to this two in a stored procedure and it won't let me alter the procedure, which makes no sense, it clearly could work. Throws same error on alter procedure. I must have into #tbl in every if because every if has select with different columns and I don't want to make table for every select. Other option is dynamic selects...

I had to make it dynamic (not all), like this:

IF @Type = '1'
    SELECT
        col1
    INTO #tbl1
    FROM Table1
ELSE IF @Type = '2'
    SELECT
        col1
        ,col2
    INTO #tbl2
    FROM Table2
--IF...

DECLARE @sql VARCHAR(MAX) = '
    select 
        *
    from #tbl' + @Type

EXEC (@sql)

But in your case, you should create temp table manually because you are using same structure and just adding value.

If you are lazy to create temp table with all the columns, another solution would be this:

ALTER PROCEDURE sp_Member (
    @comcode INT = 0
  , @SubComCode INT = 0
  )
AS
BEGIN
  SET NOCOUNT ON

  SELECT  CONVERT(DECIMAL(18, 2), 0) TurnOver
        , *
  INTO    #result
  FROM    tbmember
  WHERE   can_flag = 'N'

  IF @comcode = '0'
  BEGIN
    UPDATE  r
    SET     TurnOver = (SELECT  SUM(amount)
                        FROM    tbcoudet
                        WHERE   memcode = r.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
                       )
    FROM    #result r
  END
  IF @SubComCode = '0'
  BEGIN
    UPDATE  r
    SET     TurnOver = (SELECT  SUM(amount)
                        FROM    tbcoudet
                        WHERE   memcode = r.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
                       )
    FROM    #result r
  END

  SELECT TOP 10
          *
  FROM    #result
  WHERE   TurnOver IS NOT NULL
  ORDER BY TurnOver DESC
END