Main problem is about changing the index of rows to 1,2,3.. where contact-id and type is the same. but all columns can contain exactly the same data because of some ex-employee messed up and update all rows by contact-id and type. somehow there are rows that aren't messed but index rows are same. It is total chaos.
I tried to use an inner cursor with the variables coming from the outer cursor. But It seems that its stuck in the inner cursor.
A part of the query looks like this:
Fetch NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
DECLARE INNER_CURSOR Cursor
FOR
SELECT * FROM CONTACTS
where CONTACT_ID = @CONTACT_ID
and TYPE = @TYPE
Open INNER_CURSOR
Fetch NEXT FROM INNER_CURSOR
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
What can be the problem? Is @@FETCH_STATUS ambiguous or something?
EDIT: everything looks fine if i don't use this code inside inner cursor:
UPDATE CONTACTS
SET INDEX_NO = @COUNTER
where current of INNER_CURSOR
EDIT: here is the big picture:
BEGIN TRAN
DECLARE @CONTACT_ID VARCHAR(15)
DECLARE @TYPE VARCHAR(15)
DECLARE @INDEX_NO SMALLINT
DECLARE @COUNTER SMALLINT
DECLARE @FETCH_STATUS INT
DECLARE OUTER_CURSOR CURSOR
FOR
SELECT CONTACT_ID, TYPE, INDEX_NO FROM CONTACTS
WHERE
CONTACT_ID IN (SELECT CONTACT_ID FROM dbo.CONTACTS
WHERE CONTACT_ID IN(...)
GROUP BY CONTACT_ID, TYPE, INDEX_NO
HAVING COUNT(*) > 1
OPEN OUTER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE, @INDEX_NO
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @COUNTER = 1
DECLARE INNER_CURSOR CURSOR
FOR
SELECT * FROM CONTACTS
WHERE CONTACT_ID = @CONTACT_ID
AND TYPE = @TYPE
FOR UPDATE
OPEN INNER_CURSOR
FETCH NEXT FROM INNER_CURSOR
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
UPDATE CONTACTS
SET INDEX_NO = @COUNTER
WHERE CURRENT OF INNER_CURSOR
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM INNER_CURSOR
END
CLOSE INNER_CURSOR
DEALLOCATE INNER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE, @INDEX_NO
END
CLOSE OUTER_CURSOR
DEALLOCATE OUTER_CURSOR
COMMIT TRAN
You have a variety of problems. First, why are you using your specific @@FETCH_STATUS values? It should just be @@FETCH_STATUS = 0.
Second, you are not selecting your inner Cursor into anything. And I cannot think of any circumstance where you would select all fields in this way - spell them out!
Here's a sample to go by. Folder has a primary key of "ClientID" that is also a foreign key for Attend. I'm just printing all of the Attend UIDs, broken down by Folder ClientID:
Declare @ClientID int;
Declare @UID int;
DECLARE Cur1 CURSOR FOR
SELECT ClientID From Folder;
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @ClientID;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Processing ClientID: ' + Cast(@ClientID as Varchar);
DECLARE Cur2 CURSOR FOR
SELECT UID FROM Attend Where ClientID=@ClientID;
OPEN Cur2;
FETCH NEXT FROM Cur2 INTO @UID;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Found UID: ' + Cast(@UID as Varchar);
FETCH NEXT FROM Cur2 INTO @UID;
END;
CLOSE Cur2;
DEALLOCATE Cur2;
FETCH NEXT FROM Cur1 INTO @ClientID;
END;
PRINT 'DONE';
CLOSE Cur1;
DEALLOCATE Cur1;
Finally, are you SURE you want to be doing something like this in a stored procedure? It is very easy to abuse stored procedures and often reflects problems in characterizing your problem. The sample I gave, for example, could be far more easily accomplished using standard select calls.