While Loop - Cursor Alternative

I don't like cursors much. They're simple to write, easy to implement and good starting point for understanding loops. However, they are slow and memory-thirsty...(is that the right phrase?). So here is an example of using a simple WHILE loop which has the same behaviour as a cursor but it much faster and uses less memory than a cursor.

 DECLARE @RecordCount int
 DECLARE @RowCount int

 DECLARE @Field1 varchar(100)
 DECLARE @Field2 int
 DECLARE @Field3 char(2)

 select distinct IDENTITY(int,1,1) as ID, Field1, Field2, Field3into #loop from dbo.TableName order by ID DESC
 
 SET @RecordCount = (select max(ID) from #loop)
 SET @RowCount = 1

 WHILE @RowCount <= @RecordCount
 BEGIN
  
  select @Field1 = Field1, @Field2 = Field2, @Field3 = Field3 from #loop where ID = @RowCount

  EXECUTE [dbo].[usp_RandomCodeToReplace] 
    @Field1
   ,@Field2
   ,@Field3 

  SET @RowCount = @RowCount + 1

 END

 DROP TABLE #loop

Rank and pick best record SQL

I used to use loops for this kind of thing. This is far more efficient.

WITH Table AS
( 
SELECT
    Field1, Field2, 
    Rank() OVER (PARTITION BY Field2 ORDER BY Field1 desc) AS Rank 
FROM dbo.TableName 
)
SELECT * from Table WHERE Rank = 1

;

Hope this helps.

Basic Cursor and Loop usage with examples

This is a basic cursor, useful for smaller amounts of data.  It enables you to carry out a query against each record independantly before moving on to the next one. 

declare @Parameter1 varchar(50)

declare @Parameter2 varchar(50)

declare cur_cursor cursor for (select fieldname1,fieldname2
                                                           from server.[database].[dbo].[table with data])

open cur_cursor

Fetch Next from cur_cursor into @Parameter1, @Parameter2

While @@fetch_status = 0

begin

update table
set fieldname = @Parameter1
where otherfieldname = @Parameter2

Fetch Next from cur_cursor into @Parameter1, @Parameter2

end
close cur_cursor
deallocate cur_cursor

The following cursor uses dynamic SQL, which can allow you to add columns to a table, or deduce which columns you wish to query. 
declare @Parameter1 varchar(50)

declare @Parameter2 varchar(50)

declare @SQL nvarchar(4000)

declare cur_cursor cursor for (select fieldname1,fieldname2

                              from server.[database].[dbo].[table with data])

open cur_cursor

Fetch Next from cur_cursor into @Parameter1, @Parameter2

While @@fetch_status = 0

begin

set @SQL = 'update tablename

                set fieldname = ''test' + @Parameter2 + '''
           where otherfield = ''' + @Parameter1 + ''''

exec sp_execute @SQL

Fetch Next from cur_cursor into @Parameter1, @Parameter2

end

close cur_cursor
deallocate cur_cursor

There will be more to follow with more detailed explanations, examples of loops for larger datasets and nested loops, after the weekend.


Any questions let me know.