cfZen

Paginating Records in CF with One SQL Server 2005 call (update 1)

Posted : Jun 9, 2008 11:18 AM

ColdFusion, SQL Server 2005

I've been meaning to update folks on the excellent use I've been getting out of this way of paginating records with SQL Server 2005 via CF, and James Allen's comment today gave me an excuse to finally update folks:

Note that I've been using this method in production at www.LandsofAmerica.com - see some example search results, as well as all our sister sites: www.landsofTexas.com, www.landsofCalifornia.com, etc. Our combined network gets upwards of 15 ColdFusion requests per second, and this dynamic stored procedure has held up extremely well, despite the enormous load and wide variety of possible WHERE clauses in the search.
And now to answer James's comment, "I've been using your SP extensively around my new site and it's working great. However, I am now tring to get it to work with a table join and I just can't get it to work with a table or alias prefix (in the columns parameter)." - The "sample DAO CFC method" code below should give you what you need to fix the issue you refer to. :)

First, the Stored Procedure SQL again:

USE [propertyControlCenter]
GO
/****** Object:  StoredProcedure [dbo].[sproc_pagination]    Script Date: 06/09/2008 11:28:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================== 
-- Author:        Aaron Longnion 
-- Create date: 10/18/2007 
-- Description:    A gereral-use way of dynamically 
--        creating SQL to easily paginate the results; 
--        also includes a total record count based on 
--        the search criteria in the WHERE clause 
-- ================================================== 
CREATE PROCEDURE [dbo].[sproc_pagination] 
    -- Add the parameters for the stored procedure here 
    @SqlColumns VARCHAR(MAX), 
    @SqlFriendlyColumns VARCHAR(MAX), 
    @SqlTableClause VARCHAR(MAX), 
    @StartRow INT, 
    @EndRow INT, 
    @SqlWhere VARCHAR(MAX), 
    @SqlRowNumOrderBy VARCHAR(MAX), 
    @SqlOuterOrderBy VARCHAR(MAX) 
AS 
DECLARE @rsSQL NVARCHAR(MAX) 
DECLARE @rcSQL NVARCHAR(MAX) 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 
    -- build pagination SQL, using StartRow and EndRow to determine 
    -- which results to output 
    SET @rsSQL = N' WITH tempTable AS ( ' + 
        N' SELECT ' + 
            @SqlColumns + 
        N' , ROW_NUMBER() OVER(ORDER BY ' + 
            @SqlRowNumOrderBy + 
        N' ) AS RowNumber ' + 
        N' FROM ' + 
            @SqlTableClause 
    IF @SqlWhere + '' <> '' 
        BEGIN 
            SET @rsSQL = @rsSQL +                  
                N' WHERE ' + 
                    @SqlWhere 
        END 
    SET @rsSQL = @rsSQL + 
        N' ) SELECT ' + 
            @SqlFriendlyColumns + 
        N' FROM tempTable ' + 
        N' WHERE RowNumber >= ' + 
            CAST(@StartRow AS NVARCHAR(32)) + 
        N' AND RowNumber <= ' + 
            CAST(@EndRow AS NVARCHAR(32)) + 
        N' ORDER BY ' + 
            @SqlOuterOrderBy 
     
    -- uncomment PRINT to debug 
    --PRINT @rsSQL 
    EXEC sp_executesql @rsSQL 
    -- build second recordset simple for the count 
    SET @rcSQL = 
            N'SELECT COUNT(*) AS CountAll FROM ' + 
                @SqlTableClause 
    IF @SqlWhere + '' <> '' 
        BEGIN 
            SET @rcSQL = @rcSQL +                  
                N' WHERE ' + 
                    @SqlWhere 
        END 
     
    EXEC sp_executesql  @rcSQL     
    SET NOCOUNT OFF; 
END 

PRINT 'sproc_pagination created'

Second, some sample Gateway CFC method code:

	<cffunction name="searchInventory"
				output="false"
				access="public"
				returntype="struct"
				hint="Returns total count and recordset based on search criteria">

		<cfargument name="SearchCrit"
					required="true"
					type="struct"
					hint="Search criteria, usually passed in from the URL scope" />

		<cfset var retStruct = StructNew() />

		<cfparam name="Arguments.SearchCrit.SiteStateID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.State_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Region_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.County_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Style" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Style_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.SqFt" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Price" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.AuctionDateStart" type="string" default="#DateFormat(Now(), 'yyyy-mm-dd')#" />
		<cfparam name="Arguments.SearchCrit.AuctionDateEnd" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.ListingType" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.PageNum" type="numeric" default="1" />
		<cfparam name="Arguments.SearchCrit.RecordsPerPage" type="numeric" default="15" />
		<cfparam name="Arguments.SearchCrit.sortby" type="string" default="sqft" />
		<cfparam name="Arguments.SearchCrit.sortbyDirection" type="string" default="DESC" />

		<!--- derive start and end rows --->
		<cfset Arguments.StartRow = ( (Arguments.SearchCrit.PageNum-1) * Arguments.SearchCrit.RecordsPerPage )+1 />
		<cfset Arguments.EndRow = (Arguments.StartRow + Arguments.SearchCrit.RecordsPerPage)-1 />

		<cfset retStruct = DAO.searchInventory( ArgumentCollection=Arguments ) />

		<cfset retStruct.PagingData = Server.Utilities.Toolbox.getPagingLinks(
															RecordsPerPage = Arguments.SearchCrit.RecordsPerPage
															, RecordCount = retStruct.RC
															, NumberOfLinks = 10
															, RecordLimit = 1000000
															, QSItemsToStrip = "messageCode,searchIt"
															) />

		<cfreturn retStruct />

	</cffunction>

Third, a sample DAO CFC method. This is the key: see how the "friendly" (no table aliases) names of the columns are in the "local.SqlFriendlyCols" section. Notice how the column names in the "local.SqlCols" clause contain references to table aliases in "local.SqlTableClause". The only caveat for this, which I haven't found a work-around yet, is that you'll get an "ambiguous column" SQL error if you have a JOIN and try to sort by a column that does not have a unique name :

	<cffunction name="searchInventory" access="package" output="false" returntype="struct">

		<cfset Var local = StructNew()>

		<!--- columns --->
        <cfsavecontent variable="local.SqlCols">
            I.listing_city,
			ST.State_name,
			ST.State_shortCode,
			S.listing_type,
			I.listing_price,
			I.listing_photo,
			I.listing_id,
			I.listing_state_id,
			I.listing_lot_size,
			I.listing_county_id,
			C.county_name
        </cfsavecontent>

		<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
        <cfsavecontent variable="local.SqlFriendlyCols">
            listing_city,
			State_name,
			State_shortCode,
			listing_type,
			listing_price,
			listing_photo,
			listing_id,
			listing_state_id,
			listing_lot_size,
			listing_county_id,
			county_name
        </cfsavecontent>

		<!--- tables --->
		<cfsavecontent variable="local.SqlTableClause">
				listing_(nolock) I
			INNER JOIN
				listing_types(NOLOCK) S
				ON S.listing_type_ID = I.listing_type_ID
			INNER JOIN
				counties(nolock) C
				ON C.County_ID = I.listing_County_ID
			INNER JOIN
				states(NOLOCK) ST
				ON ST.State_ID = I.listing_State_ID			
		</cfsavecontent>

		<!--- conditions --->
        <cfoutput>
            <cfsavecontent variable="local.SqlWhere">
				I.listing_Active = 1
				AND I.listing_Acct_Active = 1
				AND I.listing_Status = 0
				<!--- dynamic info --->
				<cfif Len(Arguments.SearchCrit.siteStateID)>
					AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.State_ID)>
					AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.Region_ID)>
					AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.County_ID)>
					AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.listing_type)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
				<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.SqFt)>
					AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
					AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
				</cfif>
				
				<cfif ListLen(Arguments.SearchCrit.listing_Price)>
					AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
					AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
				</cfif>
            </cfsavecontent>
        </cfoutput>

		<!--- order --->
		<cfoutput>
	        <cfsavecontent variable="local.SqlRowNumOrderBy">
	            #Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
	        </cfsavecontent>
			<!--- used for order column that has an alias prefix --->
	        <cfsavecontent variable="local.SqlOuterOrderBy">
	            #ListLast(local.SqlRowNumOrderBy, ".")#
	        </cfsavecontent>
		</cfoutput>

		<!--- dynamically get the data based on the SQL built above --->
        <cfset local.returnStruct = Server.DAO.Common.getPaginatedData(	SqlCols = local.SqlCols,
																		SqlFriendlyCols = local.SqlFriendlyCols,
																		SqlTableClause = local.SqlTableClause,
																		StartRow = Arguments.StartRow,
																		EndRow = Arguments.EndRow,
																		SqlWhere = local.SqlWhere,
																		SqlRowNumOrderBy = local.SqlRowNumOrderBy,
																		SqlOuterOrderBy = local.SqlOuterOrderBy ) />

		<cfreturn local.returnStruct />

	</cffunction>

And finally, the getPaginatedData() method:

	<cffunction name="getPaginatedData" access="public" output="false" returntype="struct" hint="generic way to get pagination data from the dB">

		<cfargument name="SqlCols"
					type="string"
					required="true"
					hint="list of table columns ('col1, col2, col3', 'a.col1, b.col2, c.col3', or 'a.col1 AS acol, b.col2 AS bcol, (SELECT c.col3 FROM c_table) AS subCol3' are all acceptable" />

		<cfargument name="SqlFriendlyCols"
					type="string"
					required="true"
					hint="list of table columns ('col1, col2, col3' is acceptable" />

		<cfargument name="SqlTableClause"
					type="string"
					required="true"
					hint="list of table columns (most JOINs and sub-SELECTs are acceptable" />

		<cfargument name="StartRow"
					type="numeric"
					required="true"
					hint="beginning row number to display" />
		<cfargument name="EndRow"
					type="string"
					required="true"
					hint="last row number to display " />

		<cfargument name="SqlWhere"
					type="string"
					required="true"
					hint="WHERE clause filters" />

		<cfargument name="SqlRowNumOrderBy"
					type="string"
					required="true"
					hint="usually the PK of the primary table" />

		<cfargument name="SqlOuterOrderBy"
					type="string"
					required="true"
					hint="chose from the columns from SqlFriendlyCols" />

		<cfset var returnStruct = StructNew() />

		<cfstoredproc procedure="sproc_pagination" datasource="#Variables.DSNs.primary#">

			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlColumns" value="#Trim(Arguments.SqlCols)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlFriendlyColumns" value="#Trim(Arguments.SqlFriendlyCols)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlTableClause" value="#Trim(Arguments.SqlTableClause)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="SqlStartRow" value="#Arguments.StartRow#">
			<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="EndRow" value="#Arguments.EndRow#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlWhere" value="#Trim(Arguments.SqlWhere)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlRowNumOrderBy" value="#Trim(Arguments.SqlRowNumOrderBy)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlOuterOrderBy" value="#Trim(Arguments.SqlOuterOrderBy)#">
			<!--- return data + count --->
			<cfprocresult name="rs" resultset="1">
			<cfprocresult name="recordcount" resultset="2">

        </cfstoredproc>

		<cfset returnStruct.rs = rs />
        <cfset returnStruct.rc = recordcount.countAll />

		<cfreturn returnStruct />

	</cffunction>

Let me know what you think.

	<cffunction name="searchInventory" access="package" output="false" returntype="struct">

		<cfset Var local = StructNew()>

		<!--- columns --->
        <cfsavecontent variable="local.SqlCols">
            I.listing_city,
			ST.State_name,
			ST.State_shortCode,
			S.listing_type,
			I.listing_price,
			I.listing_photo,
			I.listing_id,
			I.listing_state_id,
			I.listing_lot_size,
			I.listing_county_id,
			C.county_name
        </cfsavecontent>

		<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
        <cfsavecontent variable="local.SqlFriendlyCols">
            listing_city,
			State_name,
			State_shortCode,
			listing_type,
			listing_price,
			listing_photo,
			listing_id,
			listing_state_id,
			listing_lot_size,
			listing_county_id,
			county_name
        </cfsavecontent>

		<!--- tables --->
		<cfsavecontent variable="local.SqlTableClause">
				listing_(nolock) I
			INNER JOIN
				listing_types(NOLOCK) S
				ON S.listing_type_ID = I.listing_type_ID
			INNER JOIN
				counties(nolock) C
				ON C.County_ID = I.listing_County_ID
			INNER JOIN
				states(NOLOCK) ST
				ON ST.State_ID = I.listing_State_ID			
		</cfsavecontent>

		<!--- conditions --->
        <cfoutput>
            <cfsavecontent variable="local.SqlWhere">
				I.listing_Active = 1
				AND I.listing_Acct_Active = 1
				AND I.listing_Status = 0
				<!--- dynamic info --->
				<cfif Len(Arguments.SearchCrit.siteStateID)>
					AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.State_ID)>
					AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.Region_ID)>
					AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.County_ID)>
					AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.listing_type)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
				<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.SqFt)>
					AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
					AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
				</cfif>
				
				<cfif ListLen(Arguments.SearchCrit.listing_Price)>
					AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
					AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
				</cfif>
            </cfsavecontent>
        </cfoutput>

		<!--- order --->
		<cfoutput>
	        <cfsavecontent variable="local.SqlRowNumOrderBy">
	            #Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
	        </cfsavecontent>
			<!--- used for order column that has an alias prefix --->
	        <cfsavecontent variable="local.SqlOuterOrderBy">
	            #ListLast(local.SqlRowNumOrderBy, ".")#
	        </cfsavecontent>
		</cfoutput>

		<!--- dynamically get the data based on the SQL built above --->
        <cfset local.returnStruct = Server.DAO.Common.getPaginatedData(	SqlCols = local.SqlCols,
																		SqlFriendlyCols = local.SqlFriendlyCols,
																		SqlTableClause = local.SqlTableClause,
																		StartRow = Arguments.StartRow,
																		EndRow = Arguments.EndRow,
																		SqlWhere = local.SqlWhere,
																		SqlRowNumOrderBy = local.SqlRowNumOrderBy,
																		SqlOuterOrderBy = local.SqlOuterOrderBy ) />

		<cfreturn local.returnStruct />

	</cffunction>

tags:

ColdFusion, SQL Server 2005

10 Comments ||

Add Comment

James Allen said:

You sir are a total gent!

I'm blown away by how quickly you got back to me on this. I hoped for a comment answer at some point, but not a full blog post - awesome.

This explains things perfectly. I did wonder what FriendlyColumns was for - I have just been specifying the same columns for both parameters.

Both are needed then for this to work properly?

Oh and what does the (NOLOCK) syntax do?

Thanks again for helping me out on this. So far, using your SP has been working a treat and it's very encouraging to hear that you are running it on some high traffic sites without problems. Should work nicely on the site I'm building which is aiming for huge traffic levels once launched.

Thanks again Aaron!

posted 79 days ago
Add Comment

Reply to: this comment OR this thread

cfZen said:

@James,

Yes, FriendlyColumns are needed for it to work. :\

And more info about NOLOCK hints are found at http://www.sql-server-performance.com/articles/per... Just be careful how you use them, and you should be fine. Take care, and let me know if you have more questions.

posted 79 days ago
Add Comment

Reply to: this comment OR this thread

Dan G. Switzer, II said:

If you're using MS SQL Server 2005, there's a much easier method for pagination than using a stored procedure and that's to use the Common Table Expressions (CTEs.)

Here's an example:

with pagination as
(
   -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration
   select
      row_number() over (order by company, name) as rowNo,
      name, company, email
   from
      People
   where
      disabled = 0
   and
      status = 1
)
-- we now query the CTE table
select
   *, (select count(*) from pagination) as totalResults
from
   pagination
where
   RowNo between 11 and 20
order by
   rowNo

The "with" statement creates a virtual table that you can query through. In this example we use the row_number() function to correctly order the items (which is where the "order by" clause goes.)

Very easy to use and very efficient. CTEs are extremely powerful and allow you to solve a lot of problems that were previously very difficult to solve MS SQL Server (such as grabbing hierarchical data.)

posted 78 days ago
View Replies (2) || Add Comment

Reply to: this comment OR this thread

.: HIDE REPLIES :.

cfZen said:

@Dan,

Thanks, but if you look at the stored proc in question, it *is* indeed using a CTE, but dynamically created so that it easy to re-use for all your pagination needs (or at least most ;-) See the first post on this issue here: http://cfzen.instantspot.com/blog/2007/10/11/Pagin...

posted 78 days ago
Add Comment

Reply to: this comment OR this thread

Dan G. Switzer, II said:

I should have paid more attention to the code. I just glanced at it and quite frankly would not have thought a CTE really a candidate for stored procedure (since I find they're pretty easy to write.)

When I glanced at the parameters for the stored proc it just looked so similar to other stored procs I've used for pagination (which use the top keyword and reverse ordering) that I just assumed that's what it did.

So once again, I apologize. I should have read more carefully!!! :)

posted 78 days ago
Add Comment

Reply to: this comment OR this thread

James Allen said:

Hi Aaron,

I've just added in the friendly columns and it's working nicely now - thank you!

However, I've now got a need to use GROUP BY and I see the SP doesn't support this. Is it just a matter of adding a GROUP BY clause into it in the same way you have added in the ORDER BY?

Thanks in advance,
James.

posted 78 days ago
View Replies (1) || Add Comment

Reply to: this comment OR this thread

.: HIDE REPLIES :.

Dan G.. Switzer, II said:

@James:

Since it sounds like you've got a really complicated query, this sounds like a perfect reason to switch to a Common Table Expression (CTE.)

I just wrote a blog entry (inspired by this entry) on how to use SQL Server 2005's CTEs to paginate data:

http://blog.pengoworks.com/index.cfm/2008/6/10/Pag...

posted 78 days ago
Add Comment

Reply to: this comment OR this thread

cfZen said:

@James,

hmmmm.... I don't know. All I can say is give it a shot, and let us know how it goes. :D

posted 78 days ago
Add Comment

Reply to: this comment OR this thread

James Allen said:

Hi Aaron,

I have just finished getting the SP to work with GROUP BY and HAVING. It seems to be working nicely. I added in new params for both of these statements but I also had to modify the query which obtains the record count. The normal COUNT(*) statement doesn't work correctly when grouping it seems. The one I have used seems to handle grouping and normal queries (although I haven't fully tested yet). Here is my version of your excellent SP:

ALTER PROCEDURE [dbo].[sproc_pagination]
-- Add the parameters for the stored procedure here
@SqlColumns VARCHAR(MAX),
@SqlFriendlyColumns VARCHAR(MAX),
@SqlTableClause VARCHAR(MAX),
@StartRow INT,
@EndRow INT,
   @SqlGroupBy VARCHAR(MAX),
@SqlWhere VARCHAR(MAX),
@SqlHaving VARCHAR(MAX),
@SqlRowNumOrderBy VARCHAR(MAX),
@SqlOuterOrderBy VARCHAR(MAX)
AS
DECLARE @rsSQL NVARCHAR(MAX)
DECLARE @rcSQL NVARCHAR(MAX)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- build pagination SQL, using StartRow and EndRow to determine
-- which results to output
SET @rsSQL = N' WITH tempTable AS ( '
N' SELECT '
@SqlColumns
N' , ROW_NUMBER() OVER(ORDER BY '
@SqlRowNumOrderBy
N' ) AS RowNumber '
N' FROM '
@SqlTableClause
   IF @SqlWhere '' <> ''
BEGIN
SET @rsSQL = @rsSQL
N' WHERE '
@SqlWhere
END
   IF @SqlGroupBy '' <> ''
BEGIN
SET @rsSQL = @rsSQL
N' GROUP BY '
@SqlGroupBy
END
IF @SqlHaving '' <> ''
BEGIN
SET @rsSQL = @rsSQL
N' HAVING '
@SqlHaving
END
SET @rsSQL = @rsSQL
N' ) SELECT '
@SqlFriendlyColumns
N' FROM tempTable '
N' WHERE RowNumber >= '
CAST(@StartRow AS NVARCHAR(32))
N' AND RowNumber <= '
CAST(@EndRow AS NVARCHAR(32))
      N' ORDER BY '
@SqlOuterOrderBy

-- uncomment PRINT to debug
--PRINT @rsSQL
EXEC sp_executesql @rsSQL
-- build second recordset simple for the count
SET @rcSQL =
--N'SELECT COUNT(*) AS CountAll FROM '
         N'SELECT DISTINCT COUNT(*) OVER() AS CountAll FROM '
@SqlTableClause
   IF @SqlWhere '' <> ''
BEGIN
SET @rcSQL = @rcSQL
N' WHERE '
@SqlWhere
END
   IF @SqlGroupBy '' <> ''
BEGIN
SET @rcSQL = @rcSQL
N' GROUP BY '
@SqlGroupBy
END
IF @SqlHaving '' <> ''
BEGIN
SET @rcSQL = @rcSQL
N' HAVING '
@SqlHaving
END

   --PRINT @rcSQL

EXEC sp_executesql @rcSQL
SET NOCOUNT OFF;
END

posted 76 days ago
Add Comment

Reply to: this comment OR this thread

cfZen said:

@James - Thanks! If I need that type of functionality at some point, I'll definitely look at what you put together.

posted 76 days ago
Add Comment

Reply to: this comment OR this thread

Zen moments in a ColdFusion life

Loading...

Loading....

Loading...

Paginating Records in CF with One SQL Server 2005 call (update 1)

tags:

Search

Recent Blog Entries

Archives

tags

View all tags

cfZen

Zen moments in a ColdFusion life

Loading...

Loading....

Loading...

Paginating Records in CF with One SQL Server 2005 call (update 1)

Related Content:

tags:

Search

Recent Blog Entries

Archives

tags

View all tags