sql server paged dataset

Hi,

The WHERE clause is a bit easier than the sort.

So, on the where, you first declare your variable:

        --First, declare your where        
  DECLARE @where

-- Then initialize where just to make sure it has nothing prior to our search
       
 @where = ""

-- Define where params through a series of IF iterations

Example, @fname, @lname

If @fname is not null then
where = where  + firstname = @fname
end if

If @lname is not null then
where = where  + lirstname = @lname
end if


Then on your query, you have something like this:

select * from table WHERE A.[RANK] BETWEEN @MinPageRank AND @MaxPageRank + where

This way, they could search with onl dates or either or all of the params without generating errors.

I will have to try and see if I can figure out the sort bit in SQL.

I was able to build code for dynamic sort using frontend code.

In regard of WHERE A.[RANK] BETWEEN @MinPageRank AND @MaxPageRank
you only need to make @PageNumber (the page you want) and @NumInPage (the number of rows per page) parameters.

If you want to sort by different columns (instead of always ORDER BY Name), you can make use of CASE, see http://www.sqlteam.com/article/dynamic-order-by.

The dynamic order by uses a variable @sortorder and depending on  it's value sorts by different columns.

Bye, Olaf.

I can't use either of the methods above for the ORDER BY clause since sql server is demanding that I use "top" in the select statement:

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

>sql server is demanding that I use "top" in the select statement:

Even the simplest ROW_NUMBER() examples work without a TOP clause, that's nonsense. You are already uising ORDER BY Name in our working example, and there is no TOP clause, is there? So you're already proven wrong by your own sample code.

We're not talking about the normal ORDER BY clause, but the ORDER  BY clause of the ROW_NUMBER() line.

You didn't even try, did you? If so, can you post your test code?

Bye, Olaf.

In fact, I've tried all example presented.  I still get the following error code:

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

please refer to the original question.  here is my stored proc:

DECLARE @PageNumber INT = 50
DECLARE @NumInPage INT = 10
 
DECLARE @MinPageRank INT,
        @MaxPageRank INT

declare @direction varchar(5)
declare @field varchar(50)

SET @MinPageRank = (@PageNumber - 1) * @NumInPage + 1
SET @MaxPageRank = @PageNumber * @NumInPage
 
-- Select only those records that fit into a single page
SELECT *
FROM
 (
      SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY RowID), *
      FROM MyTable
      where MyID = 41
      ORDER BY
        CASE @direction
            WHEN 'DESC' THEN  
                        CASE @field
                              WHEN 'RowID' THEN cast(rowid as varchar(200))
                              WHEN 'Name' THEN Name
                              WHEN 'CarID' THEN cast(carid as varchar(20))
                              END
                  end DESC,
            CASE @direction
            WHEN 'ASC' THEN              
                        CASE @field
                              WHEN 'RowID' THEN cast(rowid as varchar(200))
                              WHEN 'Name' THEN Name
                              WHEN 'CarID' THEN cast(carid as varchar(20))
                              END
                  END ASC


 ) A
WHERE A.[RANK] BETWEEN @MinPageRank AND @MaxPageRank

GO

I suggested you use a CASE in the part OVER (ORDER BY Name) of your original query, because ordering by different columns means the row numbering must be made different. The paging and ordering is done via the ROW_NUMBER() and adding an order by like you did, even if it would work, would be of no value at all.

The whole thing about the paging is you give all data a row number in the ordering you want, and then simply pick out the records in row number order. What needs to change, if you want other orders is the way the data is row numbered, not a final ORDER BY.

Going back to your original query, the inner part is doing the ordering:

SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Name),
		 Name,
		 ProductNumber
	FROM Production.Product

Select allOpen in new window

This'll number the data ordered by Name, so the lowest name has RANK 1. If you want paging by ProductNumber you do

SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Productnumber),
		 Name,
		 ProductNumber
	FROM Production.Product

Select allOpen in new window

I explicitly said the part needing to change is the ORDER BY Name, the ORDER BY that is already in your query.

To apply the CASE from the article I linked, you'd do:

SELECT [RANK] = ROW_NUMBER() OVER
( ORDER BY CASE WHEN @SortOrder = 1 THEN Name ELSE ProductNumber END),
		 Name,
		 ProductNumber
	FROM Production.Product

Select allOpen in new window

Is that helping to understand the concept?

@SortOrder now would be a parameter of your stored proc and this value decides what to sort by. If you have many sort orders you want to support this get's quite long, but compiled queries are having a performance benefit over ad hoc dynamic queries.

Bye, Olaf.