TryCF Gist

trycf-gist-1664923425588-eaa04bb3-5dc0-78af-8f7a-623f718963b7.cfm

<cfset newQuery = queryNew("Column_1, Column_2, Column_3, Column_4","Integer, VarChar, Decimal, Date")>
<cfloop index="i" from="1" to="10">
	<cfset queryAddRow(newQuery)>
	<cfset querySetCell(newQuery, "Column_1", i)>
    <cfset querySetCell(newQuery, "Column_2", "Value_#i#")>
    <cfset querySetCell(newQuery, "Column_3", #i#*10000.05555555)>
    <cfset querySetCell(newQuery, "Column_4", "#dateadd("d", i, now())#")>   
</cfloop>
<cfdump var="#newQuery#">

<cffunction name="limit" returntype="query" description="WORKS LIKE MYSQL LIMIT(N,N) and added the change for SQL Server" output="false">
    <cfargument name="inQry" type="query" hint="I am the query" />
    <cfargument name="arg1" type="numeric" required="true" />
    <cfargument name="arg2" type="any" required="false" default="" />
    
    <cfscript>
    var outQry = arguments.inQry;
    var a1 = arguments.arg1-1;
    
    if(arg1 GT 1){
        outQry.RemoveRows(JavaCast( "int", 0 ), JavaCast( "int", a1 ));
    }
    
    if(arg2 neq '') {
        outQry.RemoveRows(JavaCast( "int", arg2 ),JavaCast( "int", outQry.recordcount-arg2));
    } else {
        outQry.RemoveRows(JavaCast( "int", arg2 ));
    }
    
    return outQry;
    </cfscript>
</cffunction>