Sql-server – Cursor never stops

cursorssql servert-sql

I appear to have made an error in this cursor and I can't seem to figure out what I have done wrong.

I've confirmed that the select pulls back 2 rows. But when I pass it into the cursor to pick appear the string and I can extract the exact value I need. The two rows look something like the following…

|DATAIDONTWANT|...|DATAIDONTWANT|<|1|DATAIDONTWANT|<|2|DATAIDONTWANT|...|
|DATAIDONTWANT|...|DATAIDONTWANT|<|1|DATAIDONTWANT|<|2|DATAIDONTWANT|...|

The cursor appears to grab the 1st row and continually loop around never getting onto the next row or end the program.

declare
    @clobstringP varchar(max),
    @clobstring  varchar(max);

declare SevenCursor cursor for 
    select [value] as ClobP
    from string_split(@clobstring, '>')
    where value like '%<|2|%';

open SevenCursor;
fetch next from SevenCursor into @clobstringP;

while @@FETCH_STATUS = 0
begin
    insert into [database].dbo.tablestuff ( ValueP ) 
    select file387
    from ( 
        select 
             RowId387 = row_number() over( order by ( select 1 ) )
            ,file387  = [value]
        from string_split(@clobstringP, '|')
    ) a
    where a.RowId387 = 6;

end;

close SevenCursor;
deallocate SevenCursor;

Can someone point me in the right direction?

Best Answer

As Peter has pointed out, your loop will never end since you don't do a fetch inside the loop. I prefer to have only one FETCH to maintain instead of two. So, my loop structure for a cursor is as follows:

WHILE 1 = 1
BEGIN
  FETCH NEXT FROM OrdersCursor INTO @sales_person_id, @orderdate;
    IF @@FETCH_STATUS <> 0
      BREAK

  --Whatever it is I'm doing inside the loop
END

A matter of taste which you prefer...

Related Question