Looping Cursor - 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

General SQL Server Forums

New to SQL Server Programming

Looping Cursor

Author

Topic

haydn
Starting Member

5 Posts

Posted - 2007-07-09 : 19:09:49

Hi,

I need to create a cursor that will loop through my customer database to return matching rows of data based on my select statement criteria. I have written most of it based on what I remember from my limited SQL exposure at a previous job afew years ago, but I can't remember how to make the @cust_id varaible increment by 1 and loop to the end of the customer table.

Can anyone steer me in the right direction here please?

DECLARE @cust_id INT
SET @cust_id = 371
DECLARE my_cursor CURSOR FOR
SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE POSTCODE IN (SELECT POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0 )
AND CUSTOMER_ID <> @cust_id

SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0
OPEN my_cursor
--SET @cust_id = @cust_id + 1
FETCH NEXT FROM my_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor
END
CLOSE my_cursor
DEALLOCATE my_cursor

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 19:23:42

You don't need to use cursor at all.

Can you explain what are you trying to do with your query here ?

KH
[spoiler]Time is always against us[/spoiler]

haydn
Starting Member

5 Posts

Posted - 2007-07-09 : 19:37:02

I have a database with about 1500 customers in it and some are duplicate customers with the same or similar name and address. The select query that I have written returns rows of data that are likely to be matching customers based on their identical post codes. I am manually keying in each customer number from 1 to 1500 to return the required data. Once I find a match I am merging the duplicate customers with another script that I have. I thought it would be much quicker if I could use a cursor to return all of the rows of data and then I could visually scan and look for the duplicates and then run my merge script once I have indentified the duplicates.

Any suggestions for me?

Haydn.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 21:30:36

Try this

SELECT c1.CUSTOMER_ID, c1.FULL_NAME, c1.ADDRESS_LINE1, c1.SUBURB, c1.STATE, c1.POSTCODE,
       c2.CUSTOMER_ID, c2.FULL_NAME, c2.ADDRESS_LINE1, c2.SUBURB, c2.STATE, c2.POSTCODE
FROM   CUSTOMER_LANGUAGE_DETAILS AS c1
INNER JOIN CUSTOMER_LANGUAGE_DETAILS AS c2
ON     c1.POSTCODE = c2.POSTCODE 
AND    c1.CUSTOMER <> c2.CUSTOMER
WHERE  c1.INACTIVE = 0
AND    c2.INACTIVE = 0

KH
[spoiler]Time is always against us[/spoiler]

haydn
Starting Member

5 Posts

Posted - 2007-07-09 : 22:07:23

Thanks heaps. That's a much simpler way of doing it.

Haydn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources