SQL - Running Multiple Queries in a Single Transaction

The following example shows one method of implementing a paging solution that ensures stable results are returned in all requests from the query. The query is executed in a single transaction using the snapshot isolation level, and the column specified in the ORDER BY clause ensures column uniqueness.

USE AdventureWorks2012;

GO

-- Ensure the database can support the snapshot isolation level set for the query.

IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2012') = 0

    ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;

GO

-- Set the transaction isolation level  to SNAPSHOT for this query.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

GO

-- Beging the transaction

BEGIN TRANSACTION;

GO

-- Declare and set the variables for the OFFSET and FETCH values.

DECLARE @StartingRowNumber int = 1

      , @RowCountPerPage int = 3;

-- Create the condition to stop the transaction after all rows have been returned.

WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber

BEGIN

-- Run the query until the stop condition is met.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

    OFFSET @StartingRowNumber - 1 ROWS

    FETCH NEXT @RowCountPerPage ROWS ONLY;

-- Increment @StartingRowNumber value.

SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;

CONTINUE

END;

GO

COMMIT TRANSACTION;

GO