[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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

0
dhenderson12
Asked:
dhenderson12
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now