without - update using while loop in sql server
SQL-Iterating through table records (3)
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?
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.
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
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