Question

SQL 存储过程的问题：游标已经存在，哭了。

Create Procedure [UpdateRFIDState]
@Rfid nvarchar(20)
AS
Set Nocount On
Begin
Declare @State nvarchar(20);
Declare @CarNum nvarchar(20);
Declare @LastGetIn smalldatetime;
Declare @CarInfo_Cur Cursor;
Set @CarInfo_Cur = Cursor Local For Select State, CarNum, LastGetIn From CarInfo Where RFID=@Rfid And (DateDiff(minute,lastGetin, GetDate())>2) For Update;
Open @CarInfo_Cur;
Fetch Next From @CarInfo_Cur Into @State,@CarNum,@LastGetIn;
While @@Fetch_Status=0 Begin
Print @State + ' ' +@Rfid;
If (@State='运营中') Or (@State='休息中') Begin
If @State='运营中' Update OrderCar Set Lost=1 Where CarNum=@CarNum And Lost=0;
Update CarInfo Set State='等待中', LastGetIn=GetDate() Where Current Of @CarInfo_Cur;
End
Else Begin
Update OrderCar Set State='生产完成', ProduceCptTime=GetDate() Where CarNum=@CarNum And Lost=0 And State<>'生产完成';
Update CarInfo Set State='运营中' Where Current Of @CarInfo_Cur;
End
Fetch Next From @CarInfo_Cur Into @State,@CarNum,@LastGetIn;
End
Close @CarInfo_Cur;
Deallocate @CarInfo_Cur;
Exec RecreateWaittingID;
End
第一次运行可以，可第二次时就出现如下问题：
服务器: 消息 16915，级别 16，状态 1，过程 ChkErr，行 5
名为 'My_Cursor' 的游标已存在。
等待中 01952022
语句已终止。
别人都说把游标定义为Local，我定义了，可是还是不行。
另外，写的语法是否不正确＝>
Declare @CarInfo_Cur Cursor;
Set @CarInfo_Cur=Cursor Local For Select ....
还是写成：
Declare @CarInfo_Cur Cursor Local For Select ...(这种格式在查询器里语法检查无法通过）
请高手回我，谢谢……

Answer 1

提示'My_Cursor'肯定是上次运行时没有删除。你把
Close @CarInfo_Cur;
Deallocate @CarInfo_Cur;
Exec RecreateWaittingID;
放到最后一个END后面去试试。

Answer 2

Declare CarInfo_Cur Cursor For
Select State, CarNum, LastGetIn From CarInfo Where RFID=@Rfid And (DateDiff(minute,lastGetin, GetDate())>2) For Update;
Open CarInfo_Cur;
Fetch Next From CarInfo_Cur Into ....