SQL - Iterating through table records

Question

I have created user-defined function that converts a comma-delimited string into a table. I execute this function like so:

select [String] as 'ID' from dbo.ConvertStringToTable('1,2,3,4')

The results from this query look like the following:

ID
--
1
2
3
4

In reality, I want to iterate through each of the rows in this table. However, I cannot figure out how to do this. Can someone show me some sample SQL of how to iterate through the rows of the table?

Answer 1

In SQL SERVER 2000/05/08 you can use a Cursor as shown below.

However before you go down the cursor path you should first look into the problems associated with cursors in SQL Server.

DECLARE @id VARCHAR(10)

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [String] AS 'ID' 
    FROM [dbo].[ConvertStringToTable]('1,2,3,4')
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @id
    -- do your tasks here

    FETCH NEXT FROM myCursor INTO @id

END

CLOSE myCursor
DEALLOCATE myCursor

Answer 2

Don't use the cursor if you can avoid it, normally all you really need is to join to the table that you created. If your cursor is doing an update, insert, or delete, you have a 99.9% chance of not needing a cursor. Cursors should be a technique of LAST resort not first resort. Iterating through records is almost always a poor choice in a database. Learn to think in sets.

Why should you avoid cursors? Becasue they create performance nightmares. I've changed processes from taking 24 hours or more to less than a minute by removing the curosr.