yZ6kD3zm

1Paging, sorting and filtering in a stored procedure (SQL Server)
2…
3ROW_NUMBER() OVER (
4  ORDER BY CASE @SortColumn
5    WHEN 'Name'      THEN Name
6    WHEN 'OtherName' THEN OtherName
7    …
8  END *
9  CASE @SortDirection
10    WHEN 'DESC' THEN -1
11    ELSE 1
12  END
13) AS Row
14…
15	
16…
17COUNT(*) OVER () AS TotalRows
18…
19	
20--------------------------------------------------------------------------
21create procedure dbo.EmployeesByMartialStatus
22@MaritalStatus nchar(1)
23, @sort varchar(20)
24as
25
26-- Init staging table
27if exists(
28    select 1 from sys.objects o
29    inner join sys.schemas s on s.schema_id=o.schema_id
30    and s.name='Staging'
31    and o.name='EmployeesByMartialStatus'
32    where type='U'
33)
34drop table Staging.EmployeesByMartialStatus;
35
36-- Populate staging table with sort value
37with s as (
38    select *
39    , sr=ROW_NUMBER()over(order by case @sort
40        when 'NationalIDNumber' then NationalIDNumber
41        when 'ManagerID' then ManagerID
42        -- plus any other sort conditions
43        else EmployeeID end)
44    from AdventureWorks.HumanResources.Employee
45    where MaritalStatus=@MaritalStatus
46)
47select *
48into #temp
49from s;
50
51-- And now pages
52declare @RowCount int; select @rowCount=COUNT(*) from #temp;
53declare @PageCount int=ceiling(@rowCount/20); --assuming 20 lines/page
54select *
55, Page=NTILE(@PageCount)over(order by sr)
56into Staging.EmployeesByMartialStatus
57from #temp;
58go
59
60--------------------------------------------------------------------------
61-- procedure to retrieve selected pages
62create procedure EmployeesByMartialStatus_GetPage
63@page int
64as
65declare @MaxPage int;
66select @MaxPage=MAX(Page) from Staging.EmployeesByMartialStatus;
67set @page=case when @page not between 1 and @MaxPage then 1 else @page end;
68
69select EmployeeID,NationalIDNumber,ContactID,LoginID,ManagerID
70, Title,BirthDate,MaritalStatus,Gender,HireDate,SalariedFlag,VacationHours,SickLeaveHours
71, CurrentFlag,rowguid,ModifiedDate
72from Staging.EmployeesByMartialStatus
73where Page=@page
74GO
75
76--------------------------------------------------------------------------
77-- Usage
78
79-- Load staging
80exec dbo.EmployeesByMartialStatus 'M','NationalIDNumber';
81
82-- Get pages 1 through n    
83exec dbo.EmployeesByMartialStatus_GetPage 1;
84exec dbo.EmployeesByMartialStatus_GetPage 2;
85-- ...etc (this would actually be a foreach loop, but that detail is omitted for brevity)
86
87GO