sql server paged dataset

I use sql server 2008 and i need a paged dataset.  I found several articles on the web and tried the code below (by chico drury).  I need to make the inner query that fetches the records dynamic so that I can provide the "WHERE" and "ORDER BY" clauses as pararmeters.  I have bolded the code I am referring to.  any help appreciated.

-- SQL Server 2005/2008 paging method using derived table
USE [AdventureWorks]
GO
 
-- Assume we need page 6 and records 51-60
DECLARE @PageNumber INT = 6
DECLARE @NumInPage INT = 10
 
DECLARE @MinPageRank INT,
        @MaxPageRank INT
SET @MinPageRank = (@PageNumber - 1) * @NumInPage + 1
SET @MaxPageRank = @PageNumber * @NumInPage
 
-- Select only those records that fit into a single page
SELECT *
FROM
 (
	[b]SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Name),
		 Name,
		 ProductNumber
	FROM Production.Product[/b]
 ) A
WHERE A.[RANK] BETWEEN @MinPageRank AND @MaxPageRank
GO

Open in new window

dhenderson12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi

How dynamic do you need the where clause? Dynamic SQL is a lot harder to write and debug.

Suggestion: If there are a small number of possibilities I suggest something like this:

create procedure dbo.FindPerson(
    @PersonID int = null
    , @FirstName varchar( 50 ) = null
    , @LastName varchar( 50 ) = null

select *
from dbo.Person p
where
    ( @PersonID is null or p.PersonID = @PersonID )
    and ( @FirstName is null or p.FirstName = @FirstName )
    and ( @LastName is null or p.LastName = @LastName )
;

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerCommented:
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.
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

dhenderson12Author Commented:
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."
0
Olaf DoschkeSoftware DeveloperCommented:
>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.
0
dhenderson12Author Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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

Open 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

Open 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

Open 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.