paging and sorting

[Guoqi Zheng]

Sir,

The default paging of datagrid is somehow use too much resource, so I am
using Stored procedure for the paging. You can find my Stored procedure at
the end of this message.

It works fine to do paging like this, however, I have found it difficult to
do paging and sorting at the same time.

For example, in my case, my boss asked me to do sorting on EmailAddress,
ZipCode, ActivityName and Coupon.

Any recommendation how should I achieve this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com


<Paging Stored Procedure>

-- get activity subscribers per pages.
CREATE PROCEDURE Proc_PerPageActivity
@PageNr int,
@PageSize int
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #TempTable
(
TempId int IDENTITY PRIMARY KEY,
AutoId Int,
EmailAddress varchar(100),
ZipCode char(10),
ActivityName varchar(100),
Coupon varchar(50),
DateSend datetime

)
-- fill the temp table with all the topics for the
-- specified forum retrieved from the v_Forums_Topics view
INSERT INTO #TempTable
(
AutoId,
EmailAddress,
ZipCode,
ActivityName,
Coupon,
DateSend
)
SELECT
AutoId,
EmailAddress,
ZipCode,
ActivityName,
Coupon,
DateSend
FROM
vw_ActivitySubscribers Order By AutoId DESC
-- declare two variables to calculate the range of records to extract for
the specified page
DECLARE @StartId int
DECLARE @ToId int
-- calculate the first and last ID of the range of topics we need
SET @StartId = ((@PageNr - 1) * @PageSize) + 1
SET @ToID = @PageNr * @PageSize
-- select the page of records
SELECT AutoId, EmailAddress, ZipCode, ActivityName, Coupon,
dbo.DayOnly(DateSend) as DateEnter
FROM #TempTable WHERE TempId >= @StartId AND TempId <= @ToId
Order By AutoId DESC

GO



</Paging Stored Procedure>

 

[Guoqi Zheng]

OK, I answer it myself.

The solution I have now is using Dynamic Sql, I can put sorting variable
after Order By

This sounds to be an Ok solution.

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com