Paging with coldfusion and MS SQL

I am trying to create a page that will allow users to page through records. I'd like to only query the necessary records for each page rather than capture the entire query first. I am using Microsoft SQL so I cannot use LIMIT in my query. I am working with this example but cannot replicate it without the LIMIT command. I need to figure out how to write a query with replacing the 2 variables after the LIMIT

<cfquery name="get_names" datasource="#dsn#" result="get_data">
   SELECT names
   FROM pages_names
   ORDER BY names
   LIMIT #start_record#, #records_per_page# 

Open in new window

Does anyone have an example of paging in this manner? I have some examples that seem to accomplish what I want on the SQL site but I can't get coldfusion code to work properly with it - so basically I'm fishing for some examples that you may have used. Thank you.
Who is Participating?
momi_sabagConnect With a Mentor Commented:

select names from (
SELECT names, row_number() over(order by names asc) rown
   FROM pages_names
 where rown between  #start_record# and   #start_record#+#records_per_page#
THis should do the trick for you don't need to add any logic to your query, you just use record position. You can change displayPerPage to whatever


<!--- query simulator --->
<cfset yourquery= querynew("")>
<cfset queryaddcolumn  (yourquery, "Col1", "CF_SQL_integer", ListToArray("1,2,3,4,5,6"))>
<cfset queryaddcolumn  (yourquery, "Col2", "cf_sql_varchar", ListToArray("here, there, everywhere, nowhere, anywhere,somewhere"))>
 <!--- end QS --->

<cfparam name="URL.PageIndex" default="0">
<cfset DisplayPerPage = 3>
<cfset TotalPages = (yourquery.Recordcount/DisplayPerPage)-1>
<cfset StartRow = (URL.PageIndex*DisplayPerPage)+1>
<cfset EndRow = StartRow+DisplayPerPage-1>

<cfloop query ="yourquery">
<cfif CurrentRow gte StartRow >


   <cfif CurrentRow eq EndRow>
     <cfloop index="Pages" from="0" to="#TotalPages#">
         <cfset DisplayPgNo = Pages+1>
         <cfif URL.PageIndex eq pages>
            <a href="index.cfm?PageIndex=#Pages#">#DisplayPgNo#</a>


earwig75Author Commented:
by: momi_sabagPosted on 2012-04-12 at 17:07:10ID: 37840552

I was able to work with this with some tweaking. I ended up using something like this:

      Person, Started, IDNumber
      FROM md.mytbl
      WHERE Person='#arguments.person#'
      AND Created BETWEEN '#variables.begindt#' AND '#variables.enddt# 23:59:59'
) as TBL
WHERE numrow > #start_record# and numrow <= #end_record#
WHERE numrow > #start_record# and numrow <= #end_record#

Please start using cfqueryparam on all query parameters :)  because that is just a sql injection attack waiting to happen.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.