存储过程中的临时表存在疑问 [英] Temporary table in stored procedure doubt

问题描述

- 场景

1）存储过程接受4个参数

2）第四个参数可以是'P'或'D'



- 如果它是'P'则会累积然后我插入临时表

- 如果它是'D'则累积然后我插入临时表



3）我在if和else中做第二部分...但是在这里我得到错误..

如果我删除其他部分，然后它的工作原理..



但如何将其他部分包含在临时表中



存储过程从以下开始



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





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

（



@HUBID INT，

@FROMDATE DATETIME，

@TODATE DATETIME，

@TYPE varchar（10）

）

< b r />
AS



BEGIN

设定NOCOUNT ON

打印'类型：'+ CAST（@TYPE AS VARCHAR（50））

PRINT'fromdate：'+ CAST（@FROMDATE AS VARCHAR（50））

打印'todate：'+ CAST（ @FROMDATE AS VARCHAR（50））



DECLARE @USERID VARCHAR（50），

@APDDATE 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

选择*

INTO＃SINGLEREQUEST3

来自tbl_Request

其中To_Facility_ID in（从tbl_Facility中选择facility_id，其中Hub_ID = @HUBID）

和@FROMDATE和@TODATE之间的Appointment_DateTime以及（1）中的Request_Status_ID AND Created_By =路线副本'

结束



ELSE

BEGIN

select *

INTO＃SINGLEREQUEST3

来自tbl_Request

其中FROM_Facility_ID in（选择来自tbl_Facility的facility_id，其中Hub_ID = @ HUBID）

和@FROMDATE和@TODATE之间的约会_时间和（1）中的Request_Status_ID AND Created_By ='路线副本'

结束

从＃SINGLEREQUEST3中选择*



开始尝试

BEGIN TRANSACTION



DECLARE CUR_MOVE CURSOR SCROLL



--- sp继续



- 如果我执行

执行SINGLEROUTE 574，'8/09 / 2013 00:00'，'8/09/2013 23:59'，'P'





我得到的错误



Msg 2714，Level 16，State 1，Procedure SINGLEROUTE，Line 48

已经有一个名为'＃SINGLEREQUEST3'的对象了数据库。

消息156，等级15，状态1，程序SINGLEROUTE，第50行

关键字'和'附近的语法不正确。

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

推荐答案

当你在查询中使用临时表时，在编译期间会假定表中存在名称，所以你必须在else部分使用其他名称，i现在已经改变了你的查询它会起作用。





改变程序SINGLEROUTE-- 574，'8/09/2013 00:00' ，'8 / 09/2013 23:59'，'P'

（



@HUBID INT，

@FROMDATE DATETIME，

@TODATE DATETIME，

@TYPE varchar（10）

）



AS



BEGIN

设定NOCOUNT ON

打印'类型：'+ CAST（@TYPE AS VARCHAR（50））

PRINT'fromdate：'+ CAST（@FROMDATE AS VARCHAR（50））

打印'todate：'+ CAST（ @FROMDATE AS VARCHAR（50））



DECLARE @USERID VARCHAR（50），

@APDDATE 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

从tbl_Request中选择* INTO＃SINGLEREQUEST3

其中To_Facility_ID in（选择来自tbl_Facility的facility_id，其中Hub_ID = @ HUBID）

和@FROMDATE和@TODATE之间的Appointment_DateTime以及（1）中的Request_Status_ID AND Created_By ='路由副本'

END



ELSE

BEGIN

从tbl_Request中选择* INTO＃SINGLEREQUEST4其中FROM_Facility_ID（从tbl_Facility中选择facility_id，其中Hub_ID = @HOMID）和@FROMDATE和@TODATE之间的Appointment_DateTime以及（1）中的Request_Status_ID AND Created_By =路线副本'

结束



结束



- 选择*来自＃SINGLEREQUEST3



- 开始尝试

- 开始交易



--DECLARE CUR_MOVE CURSOR SCROLL



--- sp继续



- 如果我执行

执行SINGLEROUTE 574，'8/09/2013 00:00'，'8/09/2013 23:59'，'P'





如果您有任何疑问，请告诉我..



问候，

Mubin

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

本文， http：//www.informit .com / articles / article.aspx？p = 25288& seqNum = 4 [ ^ ]，（有些）解释了您的错误原因。显然，即使代码尚未执行，并且由于IF，代码似乎应该是独占的，但这是不允许的。此外，请参阅Microsoft - http://support.microsoft.com/kb/295305#appliesto [< a href =http://support.microsoft.com/kb/295305#appliestotarget =_ blanktitle =New Window> ^ ]。

解决方法链接中提供。



Scott

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

这篇关于存储过程中的临时表存在疑问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！