This week I had to write a stored procedure for retrieving real estate properties and use the data in a ColdFusion template with pagination.  I had the following input arguments: StartRow, EndRow, PriceFrom, PriceTo and SortBy.

Since I use Fusebox, all parameters passed in the URL query string are converted to the scope “attributes”.  So, here is the procedure call:
{code type=coldfusion}
<cfstoredproc procedure=”dbo.pagination” datasource=”#application.mainDSN#”>
<cfprocparam cfsqltype=”cf_SQL_INTEGER” value=”#attributes.startRow#”  />
<cfprocparam cfsqltype=”cf_SQL_INTEGER” value=”#attributes.endRow#” />
<cfprocparam cfsqltype=”cf_SQL_INTEGER” value=”#attributes.priceFrom#” null=”#(not len(trim(attributes.priceFrom)))#” />
<cfprocparam cfsqltype=”cf_SQL_INTEGER” value=”#attributes.sortBy#” null=”#(not len(trim(attributes.sortBy)))#” />
<cfprocresult name=”qProps” />
</cfstoredproc>{/code}

You may have noticed that some parameters if not passed to this template, it will send a NULL value to the store procedure.

Since the SortBy parameter is dynamic and I need to retrieve only a subset of the query, I mean, the amount of records to fit one page, I could not use CTE because SQL Server does not allow the ORDER BY clause in the CTE (temporary table). Also, we needed to have a CASE statement in the ODER BY clause to allow dynamic sorting.

After such a pain in the neck, I did some research and found some examples on Pinal Dave blog “SQL Authority“. One of the comments on his article suggested the use of RANK(), and that was it, the solution for my problem.

Here is the stored procedure:
{code type=sql}
CREATE PROCEDURE [dbo].[Pagination]
@arg_startRow int
,   @arg_endRow int
,   @arg_priceFrom money = NULL
,   @arg_priceTo money = NULL
,   @arg_sortBy varchar(50) = NULL

AS
BEGIN
SET NOCOUNT ON;
DECLARE @temp table (
propertyUUID uniqueidentifier
,   price money
,   yearBuilt smallint
,   cityName nvarchar(50)
,   rowNum int
)
INSERT INTO @temp
SELECT
p.propertyUUID
,   p.price
,   p.yearBuilt
,   c.cityName
,   rowNum =
CASE
WHEN (@arg_sortBy = ‘price ASC’) THEN
ROW_NUMBER() OVER(ORDER BY p.price)
WHEN (@arg_sortBy = ‘price DESC’) THEN
ROW_NUMBER() OVER(ORDER BY p.price DESC)
— default order
ELSE ROW_NUMBER() OVER(ORDER BY c.cityName)
END
FROM dbo.properties p
LEFT JOIN dbo.cities c ON c.cityCode = p.cityCode
WHERE p.isActive = 1
AND (@arg_priceFrom IS NULL OR p.price >= @arg_priceFrom)
AND (@arg_priceTo IS NULL OR p.price <= @arg_priceTo)
AND (@arg_cityName IS NULL OR c.cityName = @arg_cityName)
ORDER BY
CASE
WHEN @arg_sortBy = ‘price ASC’ THEN
RANK() OVER(ORDER BY p.price)
WHEN @arg_sortBy = ‘price DESC’ THEN
RANK() OVER(ORDER BY p.price DESC)
ELSE c.cityName
END

— Now select only the number of rows passed from the pagination request
SELECT
propertyUUID
,   cityName
,   price
,   yearBuilt
FROM @temp
WHERE rowNum BETWEEN @arg_startRow AND @arg_endRow
{/code}

With that SP we can now paginate without losing the order by set by the url parameter.

Thanks Pinal Dave!

2 thoughts on “ColdFusion – SQL: Dynamic Values in Order By Clause

  1. It is my pleasure when such a respected person like you acknowledges the work.

    I am personally big fan of ColdFusion and I use it till today.

Leave a Reply