Robust logic for handling >512 rows returned in SQL query

Discussion created by pdapel on Dec 12, 2013
Latest reply on Jul 10, 2017 by ElwynnMartin

We use ITPAM to provide a consistent web-services method for accessing SQL data, amongst other things.  Normally our queries return a smallish number of records.  Occasionally we have a query that returns more than the 512 maximum allowed rows within ITPAM.  Here's a way to handle more than 512 rows safely and easily.  I used ITPAM 4.1 (without SP1).  I would like to know if anybody else has come up with a different or better way to handle the possibility of more than 512 rows being returned.

(Note: This example shows SQL Server 2012 syntax, but you should be able to adapt this easily to any SQL server that supports OFFSET/LIMIT parameters.)

The Process consists of 3 operators: 

  1. An initial Query Database operator to get the row count
  2. A Loop Operator to split the data query into batches
  3. A Query Database operator inside the Loop Operator to fetch the data records

The initial Query Database operator performs a simple count() query to determine the number of expected rows, which drives the number of batches required.

Expression ="select count(1) as 'Count' from (select ...) as X"

Post-Execution code:

var numRecordsReturned = Process[OpName].QueryResults.ResultSets[0].Rows[0].Count;

Process.numRecordsReturned = numRecordsReturned;

var numLoopsRequired = Math.ceil(numRecordsReturned / 512);

Process.numLoopsRequired = numLoopsRequired;
The Loop operator has:
Repeat Count = Process.numLoopsRequired
Pre-Execution code:
var rowOffset = (Process[OpName].CurrentLoopIteration) * 512;

Process.rowOffset = rowOffset;
The Query Database operator inside the Loop has:
Expression = "select myColumnName from myTableName OFFSET " + Process.rowOffset + " ROWS FETCH NEXT 512 ROWS ONLY"

Post-Execution code:

var endingIndex=Process.rowOffset;


for (  var i=0;   i<Process[OpName].QueryResults.ResultSets[0].Rows.length;   i++) {

  var actualIndex = endingIndex + i;

  Process.OutputList[actualIndex] = Process[OpName].QueryResults.ResultSets[0].Rows[i].myColumnName;

The finished product is an accessible Process-level string array called OutputList which contains all the SQL records in the original query result order, even if you have more than 512 values.  I like this method a lot better than looking for the TruncateWarning, exception handling it, and trying to figure out which records were truncated.
Hope this helps!