Introduction
When we need to process a rowset in the cycle usually we open cursor over it and then make any processing.
For me it is not always fast and convinient in following cases:
1. Selected data stored in table variable in a body of stored procedure
2. Syntax of cursor creation and fetching is difficult (we need create and free cursor, because cursor is a system object)
3. Sometimes we can not use cursor because of changing data used by cursor filter (so cursor can refetch same record again - this can be avoided with additional calculations, but anyway...).
Background
The main Idea is to prepare data for sequential processing without cursor (preparation may be done on client or on server side), after that rowset can be simply processed in WHILE expression.
Preparation of data includes following:
Rowset should have a primary key or some unique field wich can be iterated sequentially - usually INT or BIGINT.
To get that we can use ROW_NUMBER
function on SQL server side, or just set some field in CYCLE when preparing on CLIENT side. Or you can use IDENTITY
definition in temp variable.
Using the code
Source data table:
CREATE TABLE [SampleData]
(
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_SampleData] PRIMARY KEY CLUSTERED ( [Id] ASC )
)
Source data:
INSERT INTO [SampleData]
(Name)
VALUES
('Alex'),
('Anna'),
('Tom'),
('Jerry'),
('Rupert'),
('Arnold')
Prepare data:
Using ROW_NUMBER
:
DECLARE @preparedData TABLE (ROW int, Id uniqueidentifier, Name nvarchar(100))
INSERT INTO @preparedData
SELECT ROW_NUMBER() OVER (ORDER BY Name), Id, Name
FROM [SampleData]
The same using IDENTITY
:
DECLARE @preparedData TABLE ([ROW] int IDENTITY(1,1), [Id] uniqueidentifier, [Name] nvarchar(100))
INSERT INTO @preparedData
( [Id], [Name] )
SELECT [Id], [Name]
FROM [SampleData]
ORDER BY [Name]
Now we can simply iterate by our variable: @preparedData
DECLARE @row int = 1
DECLARE @id uniqueidentifier = null
DECLARE @name nvarchar(100) = null
SELECT @id = [Id], @name = [Name]
FROM @preparedData
WHERE [ROW] = @row
WHILE @id IS NOT NULL
BEGIN
PRINT CAST(@id as nvarchar(100)) + ' - ' + @name
SET @id = NULL
SET @name = NULL
SET @row = @row + 1
SELECT @id = [Id], @name = [Name]
FROM @preparedData
WHERE [ROW] = @row
END
Results of current processing:
070CA9AF-6E80-4ACD-9599-1AF3AF6CC42B - Alex
A8C5895F-DC77-435C-B0D5-50B80FC560B2 - Anna
46273503-0CB4-47A2-B540-DF7F06A3CA1E - Arnold
391E105B-E94F-4CF6-A1EE-118079F257F6 - Jerry
1A354AC4-B340-4B2B-8D49-A7915312E701 - Rupert
B45E7C17-0751-4A07-B319-23270F424B30 - Tom
Thoughts
Using same technique and possibility to have table variable in stored procedure, we can simply implement some bulk processing of transferred records.
Of course, remember about memory... This should not be used if you whant to process millions of records without partitioning (I meant that you should not to get all records at once, you can use some paging or portions of data).