爲什麽總是出錯？

[xiayule]

存儲過程：
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


--客戶帳齡報表
--Marshal
ALTER procedure gRptCustAging
@date1 as datetime, --查詢起始日期
@date2 as datetime, --查詢結束日期
@ordertype as smallint, --排序方式,0-客戶編碼,1-銷售員編碼
@custcode1 as nvarchar(10), --客戶編碼1
@custcode2 as nvarchar(10), --客戶編碼2
@cy_id as nchar(3) --貨幣代碼
as
if @cy_id <>'ALL'
if @ordertype = 0
Select a.cust_no,a.operator,a.empl_name,a.inv_no,
a.cust_name,a.crdate,a.due_date,
a.cy_id,a.exch_rate,
isnull(a.amt,0) as sale_amt,
isnull(a.deposit,0) as deposit,
isnull(a.deposit * a.exch_rate,0) as m_deposit,
isnull(a.master_amt,0) as m_sale_amt,a.remark,
isnull(sum(b.received_amt),0) as received_amt,
isnull(sum(b.received_amt * a.exch_rate),0) as m_received_amt,
isnull(sum(b.Unallocate_amt),0) as Unallocate_amt,
isnull(sum(b.Unallocate_amt * a.exch_rate),0) as m_Unallocate_amt,
isnull(sum(b.increase_inv_amt),0) as increase_inv_amt,
b.is_take_up,a.inv_type
into #a
From v_ucust_aging a left join v_received_info b
On a.inv_no = b.inv_no
and b.inv_kind = a.inv_kind
and b.received_date between @date1 and @date2
and b.inv_no not in('fw_advance','fw_unallocated')
and b.is_take_up = 0
Where a.cust_no between @custcode1 and @custcode2
and a.cy_id = @cy_id
and a.crdate between @date1 and @date2
Group by a.cust_no,a.cust_name,a.operator,a.empl_name,a.cy_id,a.exch_rate,
a.inv_no,a.crdate,a.due_date,a.amt,
a.master_amt,a.deposit,a.remark,b.is_take_up,a.inv_type
Having isnull(a.amt,0) - isnull(a.deposit,0) -
isnull(sum(b.received_amt),0) -
isnull(sum(b.Unallocate_amt),0) <> 0
Order by a.cust_no,a.operator,a.crdate,a.inv_no,a.inv_type
Else
Select a.cust_no,a.operator,a.empl_name,
a.inv_no,a.cust_name,a.crdate,a.due_date,
a.cy_id,a.exch_rate,
isnull(a.amt,0) as sale_amt,
isnull(a.deposit,0) as deposit,
isnull(a.deposit * a.exch_rate,0) as m_deposit,
isnull(a.master_amt,0) as m_sale_amt,a.remark,
isnull(sum(b.received_amt),0) as received_amt,
isnull(sum(b.received_amt * a.exch_rate),0) as m_received_amt,
isnull(sum(b.Unallocate_amt),0) as Unallocate_amt,
isnull(sum(b.Unallocate_amt * a.exch_rate),0) as m_Unallocate_amt,
isnull(sum(b.increase_inv_amt),0) as increase_inv_amt,
b.is_take_up,a.inv_type
into #a
From v_ucust_aging a left join v_received_info b
On a.inv_no = b.inv_no
and b.received_date between @date1 and @date2
and b.inv_kind = a.inv_kind
and b.inv_no not in('fw_advance','fw_unallocated')
and b.is_take_up = 0
Where a.operator between @custcode1 and @custcode2
and a.cy_id = @cy_id
and a.crdate between @date1 and @date2
Group by a.cust_no,a.cust_name,
a.operator,a.empl_name,a.cy_id,a.exch_rate,
a.inv_no,a.crdate,a.due_date,a.amt,
a.master_amt,a.deposit,a.remark,b.is_take_up,a.inv_type
Having isnull(a.amt,0) -
isnull(a.deposit,0) -
isnull(sum(b.received_amt),0) -
isnull(sum(b.Unallocate_amt),0) <> 0
Order by a.operator,a.cust_no,a.crdate,a.inv_no,a.inv_type
Else
if @ordertype = 0
Select a.cust_no,a.operator,a.empl_name,a.inv_no,
a.cust_name,a.crdate,a.due_date,
a.cy_id,a.exch_rate,
isnull(a.amt,0) as sale_amt,
isnull(a.deposit,0) as deposit,
isnull(a.deposit * a.exch_rate,0) as m_deposit,
isnull(a.master_amt,0) as m_sale_amt,a.remark,
isnull(sum(b.received_amt),0) as received_amt,
isnull(sum(b.received_amt * a.exch_rate),0) as m_received_amt,
isnull(sum(b.Unallocate_amt),0) as Unallocate_amt,
isnull(sum(b.Unallocate_amt * a.exch_rate),0) as m_Unallocate_amt,
isnull(sum(b.increase_inv_amt),0) as increase_inv_amt,
b.is_take_up,a.inv_type
into #a
From v_ucust_aging a left join v_received_info b
On a.inv_no = b.inv_no
and b.inv_kind = a.inv_kind
and b.received_date between @date1 and @date2
and b.inv_no not in('fw_advance','fw_unallocated')
and b.is_take_up = 0
Where a.cust_no between @custcode1 and @custcode2
and a.crdate between @date1 and @date2
Group by a.cust_no,a.cust_name,a.operator,a.empl_name,a.cy_id,a.exch_rate,
a.inv_no,a.crdate,a.due_date,a.amt,
a.master_amt,a.deposit,a.remark,b.is_take_up,a.inv_type
Having isnull(a.amt,0) - isnull(a.deposit,0) -
isnull(sum(b.received_amt),0) -
isnull(sum(b.Unallocate_amt),0) <> 0
Order by a.cust_no,a.operator,a.crdate,a.inv_no,a.inv_type
Else
Select a.cust_no,a.operator,a.empl_name,
a.inv_no,a.cust_name,a.crdate,a.due_date,
a.cy_id,a.exch_rate,
isnull(a.amt,0) as sale_amt,
isnull(a.deposit,0) as deposit,
isnull(a.deposit * a.exch_rate,0) as m_deposit,
isnull(a.master_amt,0) as m_sale_amt,a.remark,
isnull(sum(b.received_amt),0) as received_amt,
isnull(sum(b.received_amt * a.exch_rate),0) as m_received_amt,
isnull(sum(b.Unallocate_amt),0) as Unallocate_amt,
isnull(sum(b.Unallocate_amt * a.exch_rate),0) as m_Unallocate_amt,
isnull(sum(b.increase_inv_amt),0) as increase_inv_amt,
b.is_take_up,a.inv_type
into #a
From v_ucust_aging a left join v_received_info b
On a.inv_no = b.inv_no
and b.received_date between @date1 and @date2
and b.inv_kind = a.inv_kind
and b.inv_no not in('fw_advance','fw_unallocated')
and b.is_take_up = 0
Where a.operator between @custcode1 and @custcode2
and a.crdate between @date1 and @date2
Group by a.cust_no,a.cust_name,
a.operator,a.empl_name,a.cy_id,a.exch_rate,
a.inv_no,a.crdate,a.due_date,a.amt,
a.master_amt,a.deposit,a.remark,b.is_take_up,a.inv_type
Having isnull(a.amt,0) -
isnull(a.deposit,0) -
isnull(sum(b.received_amt),0) -
isnull(sum(b.Unallocate_amt),0) <> 0
Order by a.operator,a.cust_no,a.crdate,a.inv_no,a.inv_type



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

錯誤：
Server: Msg 2714, Level 16, State 1, Procedure gRptCustAging, Line 60
There is already an object named '#a' in the database.
Server: Msg 2714, Level 16, State 1, Procedure gRptCustAging, Line 94
There is already an object named '#a' in the database.
Server: Msg 2714, Level 16, State 1, Procedure gRptCustAging, Line 124
There is already an object named '#a' in the database.

[wzh1215]

这么长，原来只有一个原因，就是表名#a重复，错错提示很明显了！
这种情况楼主最好先建立#aa
create talbe #aa(....)
然后就可以用：
insert #aa
select ....
...
insert #aa
select....

[xjyin]

#a 已經建立了.

[xiayule]

實際上結構很簡單，是這樣的
if @cy_id <>'ALL'
if @ordertype = 0
select * into #a from ...
else
select * into #a from ...
else
if @ordertype = 0
select * into #a from ...
else
select * into #a from ...
我很納悶，表爲什麽會重復呢？

[wzh1215]

这是因为是一次性编译时通不过。
就像这样：
create table #aa(col1 int,col2 int)
insert #aa select ....
drop table #aa
create table #aa(col1 int)
insert #aa select ....
--从理解上应该没问题，编译时也是通不过