see also:
SQL Team - Server Side Paging With SQL Server 2005
Paging In SQL Server 2k5 And ASP.NET 2.0
1 CREATE PROCEDURE GetEmployees(
2 @LastName VARCHAR(20) = NULL,
3 @Title VARCHAR(20) = NULL,
4 @City VARCHAR(20) = NULL,
5 @PageSize INT = 5,
6 @PageNumber INT = 1,
7 @SortOrder VARCHAR(20) = 'LastName'
8 )
9 AS
10
11 SET NOCOUNT ON
12 /*
13 Let us use a CTE to simplify the code. The below CTE makes the code easier
14 to read and understand.
15 */
16 ;WITH emp AS (
17 SELECT
18 /*
19 Based on the sort order passed into the stored procedure, a Record Identifier
20 (sequential number) is generated using the ROW_NUMBER() method. The sequential
21 number is generated in the sorted order.
22 */
23 CASE
24 WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title)
25 WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate)
26 WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City)
27 -- In all other cases, assume that @SortOrder = 'LastName'
28 ELSE ROW_NUMBER()OVER (ORDER BY LastName)
29 END AS RecID,
30 LastName,
31 FirstName,
32 Title,
33 HireDate,
34 City,
35 Country,
36 PostalCode
37 FROM employees
38 WHERE
39 /*
40 Apply the filter. If the filter is specified, then apply the filter.
41 If not, ignore the filter.
42 */
43 (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
44 AND
45 (@Title IS NULL OR Title LIKE '%' + @Title + '%')
46 AND
47 (@City IS NULL OR City LIKE '%' + @City + '%')
48 )
50 /*
51 Select the final query result.
52 */
53 SELECT
54 RecID,
55 LastName,
56 Title,
57 HireDate,
58 City
59 FROM emp
60 /*
61 Apply a RANGE filter on the requested SORT ORDER to retrieve the records of the
62 current page. If the "Page Number" is 3 and "Page Size" is 30 then records 61 to
63 90 are retrieved.
64 */
65 WHERE RecID BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
66 /*
67 "RecID" is a value generated by the previous CTE based on the sort order specified
68 by the @SortOrder parameter.
69 */
70 ORDER BY RecID
No comments:
Post a Comment