Paging on SQL Server 2012
SQL Server 2008- Paging by row_number() function:
declare @JobID varchar(max);
declare @JobName nvarchar(max);
declare @CompanyName nvarchar(max);
declare @DataSource nvarchar(max);
declare @JobCategory nvarchar(max);
declare @Area nvarchar(max);
declare @PageIndex int;
declare @PageSize int;
set @PageIndex=1;
set @PageSize=20;
select
d.*
from
(
select
ROW_NUMBER() over(order by JobName asc) RowIndex, -- sortField, sortOrder
v.*
from JobDataView v
inner join
(
select distinct JobID
from JobDataSheet
where
(@JobID is null or JobID=@JobID)
and (@JobName is null or JobName like '%'+@JobName+'%')
and (@CompanyName is null or CompanyName like '%'+@CompanyName+'%')
and (@DataSource is null or DataSource=@DataSource)
and (@JobCategory is null or CategoryName=@JobCategory)
and (@Area is null or (InfoName='Area' and InfoValue like '%'+@Area+'%'))
) s on s.JobID=v.JobID
) d
where d.RowIndex > (@PageIndex-1)*@PageSize and d.RowIndex <= @PageIndex*@PageSize;
SQL Server 2012 Paging by offset|fetch:
declare @JobID varchar(max);
declare @JobName nvarchar(max);
declare @CompanyName nvarchar(max);
declare @DataSource nvarchar(max);
declare @JobCategory nvarchar(max);
declare @Area nvarchar(max);
declare @PageIndex int;
declare @PageSize int;
set @PageIndex=1;
set @PageSize=20;
select
v.*
from JobDataView v
inner join
(
select distinct JobID
from JobDataSheet
where
(@JobID is null or JobID=@JobID)
and (@JobName is null or JobName like '%'+@JobName+'%')
and (@CompanyName is null or CompanyName like '%'+@CompanyName+'%')
and (@DataSource is null or DataSource=@DataSource)
and (@JobCategory is null or CategoryName=@JobCategory)
and (@Area is null or (InfoName='Area' and InfoValue like '%'+@Area+'%'))
) s on s.JobID=v.JobID
order by v.JobName asc
offset (@PageIndex-1)*@PageSize rows
fetch next @PageSize rows only
reference :
http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx
Posts
