CODEWITHSUNDEEP
sqlsql-server
user255048
user255048

Reputation: 373

SQL - Iterating through table records

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?

Upvotes: 12

Views: 60333

Answers (4)

Atithi
Atithi

Reputation: 11

Use the below function , which takes the string and the delimiter symbol....

CREATE FUNCTION [dbo].[Udfsplitstring](@Text      VARCHAR(MAX), 
                                       @Delimiter VARCHAR(20) = ' ') 
-- @Strings table will contain values after separated by delimiter   
RETURNS @Strings TABLE ( 
  ID    INT IDENTITY PRIMARY KEY, 
  VALUE VARCHAR(MAX)) 
AS 
  BEGIN 
      DECLARE @Index INT 

      -- Set the index to -1 prior to run index through the loop   
      SET @Index = -1 

      -- Run loop till Text becomes empty   
      WHILE ( Len(@Text) > 0 ) 
        BEGIN 
            -- Getting the index of first delimiter   
            SET @Index = Charindex(@Delimiter, @Text) 

            -- Checking if there is no delimiter in Text   
            IF ( @Index = 0 ) 
               AND ( Len(@Text) > 0 ) 
              BEGIN 
                  -- Inserting text which separated by delimiter     
                  INSERT INTO @Strings 
                       VALUES (Rtrim(Ltrim(@Text))) 

                  BREAK 
              END 

            -- Checking if index found in Text then run the following script   
            IF ( @Index > 1 ) 
              BEGIN 
                  -- Inserting text after separated by delimiter     
                  INSERT INTO @Strings 
                       VALUES (LEFT(@Text, @Index - 1)) 

                  -- Separate the inserted value from text   
                  SET @Text = Rtrim(Ltrim(RIGHT(@Text, ( Len(@Text) - @Index ))) 
                              ) 
              END 
            ELSE 
              -- Separate the inserted value from text   
              SET @Text = Rtrim(Ltrim(RIGHT(@Text, ( Len(@Text) - @Index )))) 
        END 

      RETURN 
  END

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

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.

Upvotes: 5

Kane
Kane

Reputation: 16802

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

Upvotes: 15

Arthur
Arthur

Reputation: 8129

The answer would be cursors, but if there is any chance not to use cursor I would suggest to use the other solution.

Your Query looks like an SQL Server Query, so here is the Documentation for SQL Server 2008.

http://msdn.microsoft.com/en-us/library/ms190028.aspx

Upvotes: 0

Related Questions