Temporary table in stored procedure doubt

Question

0.00/5 (No votes)

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 5-Sep-13 2:04am

anurag19289

Updated 5-Sep-13 2:05am

v2

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mubin M. Shaikh · Answer 1 · 2013-09-05T02:55:00

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

scottgp · Answer 2 · 2013-09-05T02:59:00

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

Posted 5-Sep-13 2:59am

scottgp

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