Click here to Skip to main content
14,450,400 members
Rate this:
Please Sign up or sign in to vote.
See more:
--scenario
1) the stored procedure accepts 4 parameters
2) the fourth paraemeter can be 'P' or 'D'

--so accroding if it is 'P' then i m inserting into temporary table
--so accroding if it is 'D' then i m inserting into temporary table

3)i am doing the second part in if and else ...but here where i am getting error..
if i remove that else part then it works..

but how to include that else part in temporary table

Stored procedure starts as per below

--------------------------------------------------------------------------------------


ALTER PROCEDURE SINGLEROUTE-- 574,'8/09/2013 00:00','8/09/2013 23:59','P'
(

@HUBID INT,
@FROMDATE DATETIME,
@TODATE DATETIME,
@TYPE varchar(10)
)

AS

BEGIN
SET NOCOUNT ON
PRINT 'TYPE:' + CAST(@TYPE AS VARCHAR(50))
PRINT 'fromdate:' + CAST(@FROMDATE AS VARCHAR(50))
PRINT 'todate:' + CAST(@FROMDATE AS VARCHAR(50))

DECLARE @USERID VARCHAR(50),
@APPDATE DATETIME,
@REQUESTID INT,
@From_Facility_ID INT,
@FROM_ADDRESS INT,
@TO_ADDRESS INT,
@TO_FACILITY_ID INT,
@HUB_ID INT,
@NEW_ROUTEID INT,
@NEW_STOPID INT,
@NEW_STOPIDF INT,
@Username varchar(50)

IF (@TYPE='P')
BEGIN
select *
INTO #SINGLEREQUEST3
from tbl_Request
where To_Facility_ID in(select facility_id from tbl_Facility where Hub_ID=@HUBID)
and Appointment_DateTime between @FROMDATE and @TODATE and Request_Status_ID in(1) AND Created_By='Route Replica'
END

ELSE
BEGIN
select *
INTO #SINGLEREQUEST3
from tbl_Request
where FROM_Facility_ID in(select facility_id from tbl_Facility where Hub_ID=@HUBID)
and Appointment_DateTime between @FROMDATE and @TODATE and Request_Status_ID in (1) AND Created_By='Route Replica'
END
select * from #SINGLEREQUEST3

BEGIN TRY
BEGIN TRANSACTION

DECLARE CUR_MOVE CURSOR SCROLL FOR

---the sp continues

--if i execute
execute SINGLEROUTE 574,'8/09/2013 00:00','8/09/2013 23:59','P'


the error that i get

Msg 2714, Level 16, State 1, Procedure SINGLEROUTE, Line 48
There is already an object named '#SINGLEREQUEST3' in the database.
Msg 156, Level 15, State 1, Procedure SINGLEROUTE, Line 50
Incorrect syntax near the keyword 'and'.
Posted
Updated 5-Sep-13 3:05am
v2
Comments
ZurdoDev 5-Sep-13 8:25am
   
1. I may be crazy but your 2 select * into statements look identical.
2. You'll need to create the temp table and then check if exists since you have a recursive SP.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi ,

As you are using Temporary table in your query, when during compile time it will be presumed that table exists with the name , so you have to use other name in else part, i have changed your query now it will work.


alter PROCEDURE SINGLEROUTE-- 574,'8/09/2013 00:00','8/09/2013 23:59','P'
(

@HUBID INT,
@FROMDATE DATETIME,
@TODATE DATETIME,
@TYPE varchar(10)
)

AS

BEGIN
SET NOCOUNT ON
PRINT 'TYPE:' + CAST(@TYPE AS VARCHAR(50))
PRINT 'fromdate:' + CAST(@FROMDATE AS VARCHAR(50))
PRINT 'todate:' + CAST(@FROMDATE AS VARCHAR(50))

DECLARE @USERID VARCHAR(50),
@APPDATE DATETIME,
@REQUESTID INT,
@From_Facility_ID INT,
@FROM_ADDRESS INT,
@TO_ADDRESS INT,
@TO_FACILITY_ID INT,
@HUB_ID INT,
@NEW_ROUTEID INT,
@NEW_STOPID INT,
@NEW_STOPIDF INT,
@Username varchar(50)

IF (@TYPE='P')
BEGIN
select * INTO #SINGLEREQUEST3 from tbl_Request
where To_Facility_ID in(select facility_id from tbl_Facility where Hub_ID=@HUBID)
and Appointment_DateTime between @FROMDATE and @TODATE and Request_Status_ID in(1) AND Created_By='Route Replica'
END

ELSE
BEGIN
select * INTO #SINGLEREQUEST4 from tbl_Request where FROM_Facility_ID in(select facility_id from tbl_Facility where Hub_ID=@HUBID)and Appointment_DateTime between @FROMDATE and @TODATE and Request_Status_ID in (1) AND Created_By='Route Replica'
END

END

--select * from #SINGLEREQUEST3

-- BEGIN TRY
--BEGIN TRANSACTION

--DECLARE CUR_MOVE CURSOR SCROLL FOR

---the sp continues

--if i execute
execute SINGLEROUTE 574,'8/09/2013 00:00','8/09/2013 23:59','P'


let me know if you have any further query..

Regards,
Mubin
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

This article, http://www.informit.com/articles/article.aspx?p=25288&seqNum=4[^], explains (somewhat ) the reason for your error. Apparently, even though the code hasn't been executed yet, and it seems like the code should be exclusive due to the IF, it's not allowed. Also, see here from Microsoft - http://support.microsoft.com/kb/295305#appliesto[^].
Work-arounds are provided in the links.

Scott
   
Comments
anurag19289 5-Sep-13 13:02pm
   
So you mean to say that the best way to solve the problem is to create a permanent table ...and insert into that permanent table as per if condition...And finally that permanent table will be inserted in temporary table....And i can work with that temporary table in the cursor....Is there any other way ?
scottgp 6-Sep-13 14:42pm
   
Actually, it looks like the examples I found may have confused the issue, since they seem to discuss examples where the temporary table structure changed depending on the IF.
No, I wasn't talking about creating a permanent table, just creating the temporary table before the IF, and then inserting within the IF. Something like:

ALTER PROCEDURE [dbo].[SINGLEROUTE]
(

@HUBID INT,
@FROMDATE DATETIME,
@TODATE DATETIME,
@TYPE varchar(10)
)

AS
BEGIN
SET NOCOUNT ON

--create table #SINGLEREQUEST3 --if table structure known ahead of time
-- (
-- )

select TYPE, count
into #SINGLEREQUEST3
from dbo.test_temp
where 1 = 2;

IF (@TYPE = 'P')
BEGIN
insert into #SINGLEREQUEST3
select TYPE, count
from test_temp where type = @type
END

ELSE
BEGIN
insert into #SINGLEREQUEST3
select TYPE, count
from test_temp where type = @type
END

select TYPE, COUNT from #SINGLEREQUEST3;

END


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100