Error in stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error in stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-20 : 08:31:37

Partha writes "Consider the following stored procedure:

create proc test
(
@progid varchar(50)=''
)
as

if @progid=''
BEGIN
select distinct orderid,orderdate,orderyear,monthofyear,program, bramount,
orderhasreturn, orderhasfraud,orderhaschargeback,returnamount,fraudamount,chargebackamount

into #t1

from orderfact
where orderdate >= @from_dt
AND orderdate < dateadd(d,1,@to_dt)
and (orderstate=20 or orderstatedesc='SUCCESS')

select distinct orderid,return_fraud_chargeback_date,program,bramount,
orderhasreturn,orderhasfraud,orderhaschargeback,returnamount,fraudamount,chargebackamount

into #t2

from orderfact
where return_fraud_chargeback_date >= @from_dt
AND return_fraud_chargeback_date < dateadd(d,1,@to_dt)
and (orderstate=20 or orderstatedesc='SUCCESS')
END
else
BEGIN
select distinct orderid,orderdate,orderyear,monthofyear,program,bramount,
orderhasreturn,orderhasfraud,orderhaschargeback,returnamount,fraudamount,chargebackamount

into #t1

from orderfact
where orderdate >= @from_dt
AND orderdate < dateadd(d,1,@to_dt)
and program=@progid
and (orderstate=20 or orderstatedesc='SUCCESS')

select distinct orderid,return_fraud_chargeback_date,program,bramount,
orderhasreturn,orderhasfraud,orderhaschargeback,returnamount,fraudamount,chargebackamount

into #t2

from orderfact
where return_fraud_chargeback_date >= @from_dt
AND return_fraud_chargeback_date < dateadd(d,1,@to_dt)
and program=@progid
and (orderstate=20 or orderstatedesc='SUCCESS')
END

When I try to create this stored procedure,
I get the following errors:

Server: Msg 2714, Level 16, State 1, Procedure test, Line 46
There is already an object named '#t1' in the database.
Server: Msg 2714, Level 16, State 1, Procedure test, Line 57
There is already an object named '#t2' in the database

Please explain.
The tables #t1 and #t2 are logically created based on the value of @progid. Then why do I get this error?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-20 : 08:45:14

The compiler isn't smart enough to realize that because of your flow control, you will not actually create two versions of the same temp table. So you'll need to outsmart it.


create proc test 
( 
@progid varchar(50)='' 
) 
as 


select distinct orderid,orderdate,orderyear,monthofyear,program, bramount, 
orderhasreturn, orderhasfraud,orderhaschargeback,returnamount,fraudamount,chargebackamount 

into #t1 

from orderfact 
where orderdate >= @from_dt 
AND orderdate < dateadd(d,1,@to_dt) 
and (orderstate=20 or orderstatedesc='SUCCESS') 
and program=case when @progid = '' then program else @progid end


select distinct orderid,return_fraud_chargeback_date,program,bramount, 
orderhasreturn,orderhasfraud,orderhaschargeback,returnamount,fraudamount,chargebackamount 

into #t2 

from orderfact 
where return_fraud_chargeback_date >= @from_dt 
AND return_fraud_chargeback_date < dateadd(d,1,@to_dt) 
and (orderstate=20 or orderstatedesc='SUCCESS') 
and program=case when @progid = '' then program else @progid end
END

<O>

Edited by - Page47 on 06/20/2002 08:45:37

mcp111
Starting Member

44 Posts

Posted - 2002-06-21 : 14:00:22

Then how come it works fine if I use regular table names t1 and t2 instead of temp tables #t1 and #t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources