SQL Server 2000 - Breaking out of a loop

Question

Hello,

I am not good at SQL Server 2000. I have a comma-delimited list of ids. I need to see if that ID exists in a table. If it does, I want to break out of the loop with that ID saved in a variable that I can use in my stored procedure. This is what I am trying right now:

DECLARE @coreID INT
SET @coreID=NULL

DECLARE @itemID NVARCHAR(50)
DECLARE itemCursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT [String] AS 'itemID' FROM dbo.SplitListIntoTable(@myIDs)

OPEN itemCursor
FETCH NEXT FROM itemCursor INTO @itemID
  WHILE @@FETCH_STATUS = 0 BEGIN

  -- If @itemID EXISTS IN MyTable set @coreID=@itemID and Break. How do I do this?

  FETCH NEXT FROM itemCursor INTO @itemID
END

CLOSE itemCursor
DEALLOCATE itemCursor

Thank you!

Answer 1

Ideally, you shouldn't use a cursor as performance won't be great. If you can do it as a set-based statement, do that instead, maybe like this:

SELECT TOP 1 @CoreID = [String] 
FROM dbo.SplitListIntoTable(@myIDs) x
    JOIN MyTable t ON x.[String] = t.ID

However, if you have a real reason to use a cursor, you can use the BREAK statement to break out of a WHILE loop

e.g.

WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF EXISTS(SELECT * FROM MyTable WHERE Id = @ItemID)
            BEGIN
                SET @CoreId = @ItemId
                BREAK
            END        

        FETCH NEXT FROM itemCursor INTO @itemID
    END

Answer 2

I don't know how to do this using a cursor, but I supect you can do this much better (faster) with a a join. If the output of dbo.SplitListIntoTable(@myIDs) is actually an odered table, then you can output a table with another column what is say the string numer, 1, 2, 3, etc...

(I don't have sql in front of me to test this but something like)

create table t(itemNum int identity, itemId nvarchar(max))

insert into t (item id) select 1 from dbo.SplitListIntoTable(@myIDs)

Then join the two and take the top one

set @coreID = 
select top 1 @itemID 
from MyTable m
inner join t t.itemid = m.itemid
order by m.itemNum asc

of course you could use a CTE, table var or temp table too.