Cursor going into infinte loop

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 2008 Forums

Transact-SQL (2008)

Cursor going into infinte loop

Author

Topic

vishalg
Starting Member

29 Posts

Posted - 2011-03-11 : 12:32:45

Hello guys,

I have written this piece of code using cursor and its going into infinite loop. Can anybody help me point out my mistake.
Thank you


	DECLARE @CompanyID int
	DECLARE @ScheduleNumberID int
	DECLARE @LocationID int
	
		
	DECLARE @OriginalLeaseNumber NVARCHAR(50)
	DECLARE @OriginalLeaseNumber1 NVARCHAR(50)
	DECLARE @OriginalCustomerID NVARCHAR(50)
	DECLARE @OriginalLocationID NVARCHAR(50)
	DECLARE @OriginalAssetID NVARCHAR(50)
	DECLARE @FinalLeaseNumber NVARCHAR(50)
	
	DECLARE @GetAsset CURSOR
	
	SET @GetAsset = CURSOR FOR
		Select min(equip_seq_num),equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_location_id
		from [dbo].LPlusEquipmentVW
		where equip_cust_id_num in (select lease_cust_id_num from [dbo].lplusleaseVW where Lease_term_date is Null)
		group by equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_vendor_name,equip_serial_num,equip_desc,equip_ACE_basis_amt,equip_location_id
		
		
	OPEN @GetAsset
	FETCH NEXT FROM @GetAsset INTO  @OriginalAssetID, @OriginalCustomerID, @OriginalLeaseNumber, @OriginalLeaseNumber1, @OriginalLocationID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF EXISTS(Select * from [CustomerConnect].[dbo].Asset where OriginalAssetID = @OriginalAssetID)
		BEGIN
			SELECT @CompanyID = CompanyID FROM Company where OriginalCustomerID = @OriginalCustomerID
			
			
			IF(@OriginalLeaseNumber = '')	
			BEGIN
				SELECT @ScheduleNumberID = ScheduleNumberID FROM ScheduleNumber where OriginalLeaseNumber = @OriginalLeaseNumber1
				SET @FinalLeaseNumber = @OriginalLeaseNumber1
			END
			ELSE IF(@OriginalLeaseNumber1 = '')
			BEGIN
				SELECT @ScheduleNumberID = ScheduleNumberID FROM ScheduleNumber where OriginalLeaseNumber = @OriginalLeaseNumber
				SET @FinalLeaseNumber = @OriginalLeaseNumber
			END
			
			SELECT @LocationID = LocationID from Location where OriginalLocationID = @OriginalLocationID
				UPDATE U
				SET U.OriginalCustomerID = M.equip_cust_id_num,
					U.OriginalLeaseNumber = @FinalLeaseNumber,
					U.OriginalLocationID = M.equip_location_id,
					U.CompanyID = @CompanyID,
					U.ScheduleNumberID = @ScheduleNumberID,
					U.LocationID = @LocationID,
					U.OriginalAssetID = M.newequip_seq_num,
					U.[Description] = M.equip_desc,
					U.Vendor = M.equip_vendor_name,
					U.Quantity = M.QTY,
					U.SerialNumber = M.equip_serial_num,
					U.UnitPrice = M.equip_ACE_basis_amt
				FROM (Select * from [CustomerConnect].[dbo].Asset) as U
					JOIN (Select min(equip_seq_num) as newequip_seq_num, Count(Equip_lease_num) as QTY,equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_vendor_name,equip_serial_num,equip_desc,equip_ACE_basis_amt,equip_location_id
							from [dbo].LPlusEquipmentVW
							where equip_cust_id_num in (select lease_cust_id_num from [dbo].lplusleaseVW where Lease_term_date is Null)
							group by equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_vendor_name,equip_serial_num,equip_desc,equip_ACE_basis_amt,equip_location_id
							having min(equip_seq_num)=@OriginalAssetID) as M
				ON U.OriginalAssetID = M.newequip_seq_num
			PRINT @@rowcount
				
		END
		ELSE
		BEGIN
		
			
			SELECT @CompanyID = CompanyID FROM Company where OriginalCustomerID = @OriginalCustomerID
		
			IF(@OriginalLeaseNumber = '')	
			BEGIN
				SELECT @ScheduleNumberID = ScheduleNumberID FROM ScheduleNumber where OriginalLeaseNumber = @OriginalLeaseNumber1
				SET @FinalLeaseNumber = @OriginalLeaseNumber1
			END
			ELSE IF(@OriginalLeaseNumber1 = '')
			BEGIN
				SELECT @ScheduleNumberID = ScheduleNumberID FROM ScheduleNumber where OriginalLeaseNumber = @OriginalLeaseNumber
				SET @FinalLeaseNumber = @OriginalLeaseNumber
			END
			
			SELECT @LocationID = LocationID from Location where OriginalLocationID = @OriginalLocationID
			
			
			INSERT INTO [CustomerConnect].[dbo].[Asset]
           ([Description]
           ,[Vendor]
           ,[SerialNumber]
           ,[UnitPrice]
           ,[Quantity]
           ,[LocationID]
           ,[ScheduleNumberID]
           ,[CompanyID]
           ,[OriginalCustomerID]
           ,[OriginalLeaseNumber]
           ,[OriginalLocationID]
           ,[OriginalAssetID])
			Select equip_desc, equip_vendor_name,equip_serial_num,equip_ACE_basis_amt, Count(Equip_lease_num) as QTY, @LocationID,@ScheduleNumberID,@CompanyID,equip_cust_id_num,@FinalLeaseNumber,equip_location_id, min(equip_seq_num)
			from [dbo].LPlusEquipmentVW
			where equip_cust_id_num in (select lease_cust_id_num from [dbo].lplusleaseVW where Lease_term_date is Null)
			group by equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_vendor_name,equip_serial_num,equip_desc,equip_ACE_basis_amt,equip_location_id
			order by equip_orig_lease
		END
		FETCH NEXT
		FROM @GetAsset INTO @OriginalAssetID, @OriginalCustomerID, @OriginalLeaseNumber, @OriginalLeaseNumber1, @OriginalLocationID
	END
	CLOSE @GetAsset
	print 'DND'

Please help.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-11 : 12:52:25

This looks a mess.

I would suggest that you forget about the cursor and start again using MERGE. (The syntax is in BOL)

If you have problems, post some sample data and expected results.

vishalg
Starting Member

29 Posts

Posted - 2011-03-11 : 13:02:38

Sorry, I know its a mess but I can remove the irrelevant part and post the code again. Also, what I am simply trying to do is merge data from one database to the other..

Foreach row in source
IF row already exists in target.. UPDATE target row
ELSE insert row in target



        DECLARE @CompanyID int
	DECLARE @ScheduleNumberID int
	DECLARE @LocationID int
	DECLARE @OriginalLeaseNumber NVARCHAR(50)
	DECLARE @OriginalLeaseNumber1 NVARCHAR(50)
	DECLARE @OriginalCustomerID NVARCHAR(50)
	DECLARE @OriginalLocationID NVARCHAR(50)
	DECLARE @OriginalAssetID NVARCHAR(50)
	DECLARE @FinalLeaseNumber NVARCHAR(50)
	
	DECLARE @GetAsset CURSOR
	
	SET @GetAsset = CURSOR FOR
		Select min(equip_seq_num),equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_location_id
		from [dbo].LPlusEquipmentVW
		where equip_cust_id_num in (select lease_cust_id_num from [dbo].lplusleaseVW where Lease_term_date is Null)
		group by equip_cust_id_num,equip_lease_num,equip_orig_lease,equip_vendor_name,equip_serial_num,equip_desc,equip_ACE_basis_amt,equip_location_id
		
		
	OPEN @GetAsset
	FETCH NEXT FROM @GetAsset INTO  @OriginalAssetID, @OriginalCustomerID, @OriginalLeaseNumber, @OriginalLeaseNumber1, @OriginalLocationID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF EXISTS(Select * from [CustomerConnect].[dbo].Asset where OriginalAssetID = @OriginalAssetID)
		BEGIN
                         ---UPDATE
			PRINT @@rowcount
				
		END
		ELSE
		BEGIN

                        --INSERT

		END
		FETCH NEXT
		FROM @GetAsset INTO @OriginalAssetID, @OriginalCustomerID, @OriginalLeaseNumber, @OriginalLeaseNumber1, @OriginalLocationID
	END
	CLOSE @GetAsset

I hope this version is easier to find the fault.
Thank you.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-11 : 13:38:41

To get a good answer, you should take the time to post sample data for the following tables along with what [CustomerConnect].[dbo].Asset should look like after the sample data has been processed.
(ie What you want the sample data to look like before and after processing)
A cursor is NOT the way to do this.

[dbo].LPlusEquipmentVW
[CustomerConnect].[dbo].Asset
Company

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-03-11 : 17:00:30

quote:
Originally posted by vishalg

Sorry, I know its a mess but I can remove the irrelevant part and post the code again. Also, what I am simply trying to do is merge data from one database to the other..

....

I hope this version is easier to find the fault.
Thank you.

As Ifor mentioned you should not use a cursor for this sort of operation. Use MERGE. A friend of mine was doing something similar to this and the CURSOR took hours and still had not completed. I rewrote the query using MERGE (which is based on JOIN and can be optimized by the query optimizer) and the same operation using MERGE took exactly 6 seconds (a few million rows).

===
http://www.ElementalSQL.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-13 : 00:16:04

>> I know its a mess but I can remove the irrelevant part and post the code again. Also, what I am simply trying to do is merge data from one database to the other.. <<

I am very sorry for you; It is too screwed up for anyone to help ;)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources