自己改造的分页存储过程

-- =============================================

-- 创建通用的分页存储过程

-- =============================================

IF EXISTS (

  SELECT *

    FROM INFORMATION_SCHEMA.ROUTINES

   WHERE SPECIFIC_NAME = N'sp_PublicPagination'

)

   DROP PROCEDURE sp_PublicPagination

GO

 

CREATE PROCEDURE sp_PublicPagination

@TableName    nvarchar(4000)='',          --表名,如users

@PageSize     int=20,                     --每页的记录数,默认为10

@CurrentPage  int=1,                       --表示当前页1

@KeyField     nvarchar(100)='ID',         --关键字段名，默认为ID,该字段要求是表中的索引或无重复和不为空的字段

@KeyAscDesc   nvarchar(4)='ASC',          --关键字的升、降序，默认为升序ASC , 降序为DESC

@Fields       nvarchar(4000)='*',         --所选择的列名，默认为全选

@Condition    nvarchar(4000)='',          --where 条件,默认为空

@Order        nvarchar(1000)=''            --排序条件,默认为空

AS

 

if @PageSize <=0 or @CurrentPage <0

begin

    raiserror('当前页数和每页的记录数都必须大于零！',11,1)

    return

end

 

--设置是升序还是降序

if @KeyAscDesc = 'DESC'

    set @KeyAscDesc = '<'

else

    set @KeyAscDesc = '>'

 

--如果条件不为空的话

if @Condition <> ''

    set @Condition = ' where ' + @Condition

 

--以下要拼接SQL语句

declare @SQL nvarchar(4000)

 

set @SQL = ''

if @CurrentPage = 1

   set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TableName + @Condition + ' ' + @Order

else begin

   

    declare @iTopNum int

    set @iTopNum = @PageSize * (@CurrentPage - 1)

    set @SQL = @SQL + 'declare @sLastValue nvarchar(1000)' + char(13)

    set @SQL = @SQL + 'SELECT Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue=' + @KeyField + ' FROM ' + @TableName + @Condition + ' ' + @Order + char(13)

 

    declare @tempCondition nvarchar(1000)

    if @Condition = ''

       set @tempCondition = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '

    else

       set @tempCondition = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '

    set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TableName + @Condition + @tempCondition + @Order

end

 

EXECUTE sp_executesql @SQL

 

GO

-- =============================================

-- (SQL SERVER 2005)下可以这样分页

-- =============================================

USE AdventureWorks;

GO

WITH OrderedOrders AS

(SELECT SalesOrderID, OrderDate,

ROW_NUMBER() OVER (order by OrderDate)as RowNumber

FROM Sales.SalesOrderHeader )

SELECT *

FROM OrderedOrders

WHERE RowNumber between 50 and 60;