If you come from a programming background you might want to do a loop using TSQL rather than using a cursor.
The syntax for this is pretty recognisable, exempli gratia,
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @thisThing INT | |
| DECLARE @thatThing INT | |
| SET @thisThing = 1 | |
| SET @thatThing = 10 | |
| WHILE @thisThing < @thatThing | |
| BEGIN | |
| —Do stuff | |
| SELECT GETDATE() | |
| SET @thisThing = @thisThing + 1 | |
| END |
However if you want to feed in dynamic variables captured from a table you can create a distinctive row count table.
This deduplicates the occurrence of a field value and assigns it a row number.
Where this can be used will be included in a more complicated example coming in the future.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| IF OBJECT_ID('tempdb..#rowCountTB') IS NOT NULL | |
| DROP TABLE #rowCountTB; | |
| WITH DistinctTable | |
| AS ( | |
| SELECT DISTINCT [fieldValue] | |
| FROM [tableName] | |
| ) | |
| ,RowNumber | |
| AS ( | |
| SELECT ROW_NUMBER() OVER ( | |
| ORDER BY [fieldValue] | |
| ) AS RowNum | |
| ,[fieldValue] | |
| FROM DistinctTable | |
| ) | |
| SELECT * | |
| INTO #rowCountTB | |
| FROM RowNumber | |
| ORDER BY [fieldValue] |
Tidbytez 