数据库中已经有一个名为“#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屋!