Paging data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Paging data

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-16 : 11:38:44

I'm working on a php / sql server 2005 intranet project.
My gui plays nicely with the SQL server but I now need to create a method of paging the data in manageable, and easily viewable, 'chunks' of, say, 6 records per returned html/data screen.

Here is one of my queries:


       SELECT
       
       Mortgage_Number,
       Borrower_Number,
       CASE
         WHEN t2.description IS NOT NULL
           THEN t2.description
        ELSE t1.Third_Party_Transaction_Type
       END AS Third_Party_Transaction_Type,
       Date_Requested,
       Close_Date,
       CASE
         WHEN t3.description IS NOT NULL
           THEN t3.description
        ELSE t1.Trace_Action_Type
       END AS Trace_Action_Type,
       CASE
         WHEN t4.description IS NOT NULL
           THEN t4.description
        ELSE t1.Trace_Result
       END AS Trace_Result,
       Spare_field_1,
       Spare_field_2,
       Spare_field_3,
       Spare_field_4,
       Added_By_User,
       Added_By_Date,
       Amended_By_User,
       Amended_Date
       
       FROM business_details AS t1
       
       LEFT JOIN sd_third_party AS t2
        ON t1.Third_Party_Transaction_Type = t2.code
       LEFT JOIN sd_trace_action_type AS t3
        ON t1.Trace_Action_Type = t3.code 
       LEFT JOIN sd_trace_results AS t4
        ON t1.Trace_Result = t4.code 
       
       WHERE (Mortgage_Number = '$mortgage')
       
          ORDER BY [$criteria] $direction

Can anyone please suggest a way to implement paging to meet my requirements? Were I using MySQL I could use 'Limit', but SQL Server 2005 doesn't appear to have anything like that (I'm a newbie to MS SQL).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-16 : 11:42:34

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx
http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

KH
[spoiler]Time is always against us[/spoiler]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-16 : 11:58:07

MANY thanks for the links khtan. Now I'll have a good head scratch and figure it all out. :)

Just need to find a way to reconfigure my script above to be in line with the example


DECLARE	@PageSize INT,
	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT

SELECT	@PageSize = 20,
	@PageNumber = 3

SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH Members  AS
(
	SELECT	M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY,
			ROW_NUMBER() OVER (ORDER BY M_POSTS DESC) AS RowNumber,
			ROW_NUMBER() OVER (ORDER BY M_NAME DESC) AS RowNumber2
	FROM	dbo.FORUM_MEMBERS
)
SELECT	RowNumber, M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY
FROM	Members
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;

given in one of your links. Mmmm ....

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-16 : 13:06:58

Does this look right to you guys? Once I get the jist of it I can roll it out over the hundreds I need to create:


DECLARE
        @PageSize INT,
       	@PageNumber INT,
       	@FirstRow INT,
       	@LastRow INT
       
       SELECT	@PageSize = 2,
       	@PageNumber = 1
       
       SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
       	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;


WITH results AS


(  SELECT
       
       Mortgage_Number,
       Borrower_Number,
       CASE
         WHEN t2.description IS NOT NULL
           THEN t2.description
        ELSE t1.Third_Party_Transaction_Type
       END AS Third_Party_Transaction_Type,
       Date_Requested,
       Close_Date,
       CASE
         WHEN t3.description IS NOT NULL
           THEN t3.description
        ELSE t1.Trace_Action_Type
       END AS Trace_Action_Type,
       CASE
         WHEN t4.description IS NOT NULL
           THEN t4.description
        ELSE t1.Trace_Result
       END AS Trace_Result,
       Spare_field_1,
       Spare_field_2,
       Spare_field_3,
       Spare_field_4,
       Added_By_User,
       Added_By_Date,
       Amended_By_User,
       Amended_Date
       
       FROM business_details AS t1
       
       LEFT JOIN sd_third_party AS t2
        ON t1.Third_Party_Transaction_Type = t2.code
       LEFT JOIN sd_trace_action_type AS t3
        ON t1.Trace_Action_Type = t3.code 
       LEFT JOIN sd_trace_results AS t4
        ON t1.Trace_Result = t4.code 
        
        WHERE (Mortgage_Number = '$mortgage')
	       
        ROW_NUMBER OVER (ORDER BY [$criteria] $direction) AS RowNumber
        
        )
        
       SELECT
              RowNumber, 
              Mortgage_Number,
              Borrower_Number,
              Third_Party_Transaction_Type,
              Date_Requested,
              Close_Date,
              Trace_Action_Type,
              Trace_Result,
              Spare_field_1,
              Spare_field_2,
              Spare_field_3,
              Spare_field_4,
              Added_By_User,
              Added_By_Date,
              Amended_By_User,
       	      Amended_Date
       	      
       FROM results
       
       WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
       ORDER BY RowNumber ASC;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 13:17:00

This looks ok. Hope you are passing criteria and direction as parameters in which case you need to use CASE WHEN construct after ORDER BY.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-16 : 14:20:07

quote:
Originally posted by visakh16

This looks ok. Hope you are passing criteria and direction as parameters in which case you need to use CASE WHEN construct after ORDER BY.

Would you be so kind as to be more specific please? Running the script gives me an error:

'Incorrect syntax near 'ROW_NUMBER''
but I'm not sure which one is the culprit.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-16 : 19:42:21

ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber

KH
[spoiler]Time is always against us[/spoiler]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-17 : 03:44:56

quote:
Originally posted by khtan

ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber

KH
[spoiler]Time is always against us[/spoiler]

Thanks for pointing that out.

I also had the line

"ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber"
in the wrong place. It should have been

"Amended_By_User,
Amended_Date,
<------ inserted into here ----------------------->

FROM format_trace_details AS t1"

I guess that I'll now have to send the original query, without the paging stuff, to the table first in order to COUNT() how many records there are to be returned in order to calculate the number of possible pages there are to be returned. So the user can see this in the GUI and add some controls to facilitate paging.
Have I missed anything?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources