What is wrong with this code

Discussion in 'Databases' started by datropics, Sep 2, 2007.

  1. #1
    The last statement before the DECLARE is a print (and it does print to the screen) but nothing else happens. I've tested @@fetch_status and it IS 0. What am I doing wrong in this code? Thanks in advance!

    
    	DECLARE DistinctIDs CURSOR FOR
    		SELECT	DISTINCT [@ID]
    		FROM	STOS
    		ORDER 	BY [@ID];
    
    -- Process the Cursor
    	OPEN DistinctIDs
    		FETCH NEXT FROM DistinctIDs INTO @SelID
    		Print 'Getting id (status = ' + Convert(NVarChar(10), @@FETCH_STATUS) + ' )'
    		WHILE (@@fetch_status = 0) 
    		BEGIN
    			-- Get the count of that particular ID
    				SET @SelIDCount = (SELECT COUNT([@id]) FROM STOS WHERE [@id] = @SelID)
    			-- Keep a count
    				SET @TheCurrnt = @TheCurrnt + 1
    
    			Print 'Processing item ' + Convert(NVarChar(10),@TheCurrnt) + ' of ' + Convert(NvarChar(10), @TheTotal) + ': ' + Convert(NVarChar(10), @SelID) + '. Count: ' + Convert(NVarchar(10), @SelIDCount)								
    			-- Initialise sequence count
    				SET @CurrentCount = 0
    			-- Assign a new sequence for each instance of that ID
    				IF @SelIDCount > 0 
    				BEGIN
    					WHILE (@CurrentCount <= @SelIDCount)
    					BEGIN
    						SET @CurrentCount = @CurrentCount + 1
    						Print '   Processing ' + Convert(NVarChar(10), @CurrentCount) + ' of ' + Convert(NVarChar(10), @SelIDCount)
    						UPDATE STOS
    							SET [SEQUENCE.NUMBER] = @CurrentCount
    						-- Create a recordset for comparison
    						FROM (SELECT TOP 1 * FROM STOS WHERE [@ID] = @SelID and [SEQUENCE.NUMBER] = 0) AS S1
    
    						WHERE 
    							-- Compare record with this current running where...
    								(STOS.[@ID] 	= @SelID)   	AND 
    								(STOS.[@ID] 	= S1.[@ID]) 	AND 
    								(STOS.[THE-ROW-ID] = S1.[THE-ROW-ID]);
    					END
    				END
    			-- Process the next ID
    				FETCH NEXT FROM DistinctIds INTO @SelID
    				Print '   Next Item is: ' + Convert(NVarChar(10), @SelID)
    				IF ((SELECT COUNT([@ID]) FROM STOS WHERE [@id] like '%.0%') <= 0) BREAK
    		END
    	CLOSE DistinctIDs
    	DEALLOCATE DistinctIDs
    
    Code (markup):
    daTropics
     
    datropics, Sep 2, 2007 IP
  2. datropics

    datropics Peon

    Messages:
    309
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    0
    #2
    I found it!

    IF ((SELECT COUNT([@ID]) FROM STOS WHERE [@id] like '%.0%') <= 0) BREAK

    I was selecting the wrong field - duh
     
    datropics, Sep 2, 2007 IP