If @Sort Is Null Or @Sort = ''
Set @Sort = @PK
If @SortMode = 0
Set @Sort = ' Order By ' + @Sort + ' Desc'
Else
Set @Sort = ' Order By ' + @Sort + ' Asc'
/*Default Page Number*/
If @PageNumber < 1
Set @PageNumber = 1
/*Set paging variables.*/
Set @strPageSize = Convert(varchar(50), @PageSize)
Set @strSkippedRows = Convert(varchar(50), @PageSize * (@PageNumber - 1))
/*Set filter & group variables.*/
If @Filter Is Not Null And @Filter != ''
Begin
Set @strFilter = ' Where ' + @Filter + ' '
Set @strSimpleFilter = ' And ' + @Filter + ' '
End
Else
Begin
SET @strSimpleFilter = ''
SET @strFilter = ''
End
/*使用表来存放记录总数*/
Create Table #RowCount(RCount int)
Declare @GetCount varchar(1000)
Set @GetCount = 'Insert Into #RowCount (RCount) Select Count(' +@Pk +') From ' + @Tables + @strFilter
Exec(@GetCount)
Select @DataCount = RCount From #RowCount
Drop Table #RowCount
Set @PageCount =Ceiling(@DataCount / @PageSize)
/**********************************************************/
IF @PageNumber = 1 -- In this case we can execute a more efficient query with no subqueries.
Exec ('Select Top ' + @strPageSize + ' ' + @Fields + ' From ' + @Tables + @strFilter + @Sort)
ELSE -- Execute a structure of subqueries that brings the correct page.
Exec ('Select ' + @Fields + ' From ' + @Tables + ' Where ' + @PK + ' In ' + ' (Select Top ' + @strPageSize + ' ' + @PK + ' From ' + @Tables +
' Where ' + @PK + ' Not In (Select Top ' + @strSkippedRows + ' ' + @PK + ' From ' + @Tables +
@strFilter + @Sort + ') ' + @strSimpleFilter + @Sort + ') ' + @Sort)
CREATE PROCEDURE P_GetPagedOrders2005
@startIndex INT,
@pageSize INT
AS
begin
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID)
SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1
end