error "Cursor already exists" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error "Cursor already exists"

Author

Topic

SanjaySutar
Starting Member

18 Posts

Posted - 2008-10-23 : 03:37:09

Hi,
i have a stored procedure which is throwing error as Cursor "already exits".

The procedure is as follows :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spl_Mass_Status_Bags]
(@Branch_Code varchar(3),@Company_Code varchar(4),
@event_code varchar(2),@office_code varchar (5),
@location_code varchar (3),@exception_code varchar (2),
@bag_number varchar (11),@flight_number varchar (10),
@Date Varchar(15),@remarks varchar(255),@Time varchar(20),
@GMT int,@entry_by varchar(10),
@data_center varchar (5))
As
Begin

Begin Try
declare @Reference_number Varchar(20)
Declare @ErrorMessage Varchar(300)
Declare @ErrorProc Varchar(50)
Declare @shipment_id varchar(18)
Declare @tracking_number varchar(20)
Declare @GetDateTime varchar(20)
Declare @event_type char(1)

select Exception_code from Exception where Exception_code=@Exception_code
if(@@RowCount=0)
Begin
Raiserror('Exception Code does not exists',16,1)
return
End

Select Bag_number from bags_in_office Where Bag_number = @Bag_number

if(@@Rowcount>=1)
Begin
exec sp_nextSerialNumGet @company=@Company_Code,@branch=@branch_code,@division='',@location='',@office='',@serial='X1',@category='',@result=@Reference_number output
set @GetDateTime=@Date + ' ' + @Time

Insert Into status_header (reference_number,event_code,office_code,location_code,exception_code,bag_number,flight_number,status_datetime,remarks,update_datetime,entry_by,data_center) Values (@Reference_number,@event_code,@office_code,@location_code,@exception_code,@bag_number,@flight_number,@GetDateTime,@remarks,dbo.udf_gmtgetdate(@GMT),@entry_by,@data_center)
select @event_type=event_type from event_master where event_code=@event_code
Declare Fetch_Data Cursor
For
select shipment_id, tracking_number from bag_detail bd inner join bag_header bh on bh.bag_number = bd.bag_number where bh.bag_number = @bag_number
Open Fetch_Data
Fetch Next From Fetch_Data Into @Shipment_id,@Tracking_Number
If(@@Fetch_Status=-1)
Begin
Raiserror('Rows not found',16,1)
End
While @@Fetch_Status=0
Begin
declare @Status_details_id varchar(18)
exec sp_nextSerialNumGet @company=@Company_Code,@branch=@branch_code,@division='',@location='',@office='',@serial='X1',@category='',@result=@Status_details_id output

Insert into status_detail (status_detail_id,reference_number,shipment_id,tracking_number,status_datetime) Values (@Status_details_id ,@Reference_number,@shipment_id,@tracking_number,@GetDateTime)
Declare @tracker_id Varchar(18)

Exec sp_nextSerialNumGet @company=@Company_Code,@branch=@branch_code,@division='',@location='',@office='',@serial='X1',@category='',@result=@tracker_id output
Insert into package_tracker (tracker_id,event_code,reference_number,event_date_time,shipment_id,tracking_number,event_type,location_code,flight_number,bag_number,description,entry_by,entry_datetime,data_center)
Values (@tracker_id,@event_code,@Reference_number,@GetDateTime,@shipment_id,@tracking_number,@event_type,@location_code,NULL,@bag_number,@remarks,@entry_by,dbo.udf_gmtgetdate(@GMT),@data_center)

Fetch Next From Fetch_Data Into @Shipment_id,@Tracking_Number

End
Close Fetch_Data
Deallocate Fetch_Data
End
else
Begin
raiserror('Bag number not found',16,1)
End
End Try
Begin Catch
Set @ErrorProc='Procedure ' + ' ' + ERROR_PROCEDURE()
Set @ErrorMessage= @ErrorProc + ' ' + ERROR_MESSAGE()
Raiserror(@ErrorMessage,16,1)
End Catch
End

San

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 08:04:40

go into your query window.
execute statements:

Open Fetch_Data
deallocate Fetch_Data

Now it has to be cleared.

I think you have tested before without DEALLOCATE so the cursors stays already there...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-10-23 : 09:11:41

and you really should look into moving away from CURSORS....see the improvements quoted in the attached link.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113082

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources