--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'.