Viewing By Category : MSSQL / Main

Paging in MSSQL 2005-Greater? Help

Posted At : February 19, 2009 10:24 AM |

Posted By : Luis Majano

Related Categories: Coldfusion, MySQL, MSSQL

I wanted to shoot out on this as I do not use MSSQL 2005 and greater too much and need help on what would be the best and great way to do record paging. I am so used to this in MySQL and you can see a sample of MySQL below:

<cftransaction>
   <cfquery name="qUsers" datasource="#getDatasource().getName()#">
      SELECT SQL_CALC_FOUND_ROWS
          Users.user_id, Users.user_fname, Users.user_lname, Users.user_email, Users.user_isActive
       FROM wiki_users as Users
       WHERE Users.user_isActive = <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.active#">

       
       <cfif isNumeric(arguments.maxRows)>
       LIMIT <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.startRow-1#">,
             <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.maxRows#">
       </cfif>
   </cfquery>
   <cfquery name="qFoundRows" datasource="#getDatasource().getName()#" username="#getDataSource().getUsername()#" password="#getDataSource().getPassword()#">
      SELECT found_rows() AS foundRows
   </cfquery>
</cftransaction>


<cfif qUsers.recordcount>
   <cfset queryAddColumn(qUsers,"foundRows",listToArray(qFoundRows.foundRows))>
</cfif>

First of all, do you see any problems with this approach? Second, how would I reproduce this in MSSQL?

Comments (2) |

Print |

Send |

del.icio.us |

Linking Blogs

Comments

[Add Comment]

You might check out this tech article in MSDN which provides 3 different solutions for paging in MS SQL 2000.

http://msdn.microsoft.com/en-us/library/ms979197.a...

# Posted By Al |

2/19/09 12:14 PM

I generally do this with a common table expression:

WITH tempTable AS (
   SELECT
      P.FirstName
      , P.LastName
   , ROW_NUMBER() OVER(ORDER BY P.LastName, P.FirstName) AS RowNumber
   FROM
      People P (NOLOCK)
   WHERE
      P.Active = 1
)

SELECT *, (SELECT Max(RowNumber) FROM tempTable) AS Records
FROM tempTable
WHERE
   RowNumber >= 11
   AND RowNumber 20
ORDER BY
   LastName, FirstName

CTEs are your friend...

# Posted By Daniel Short |

2/19/09 7:10 PM

[Add Comment]

ColdBox 2.6.3 : RENEWED