文章地址：http://www.codeproject.com/KB/database/kuuy_P_CustomPaging.aspx
 
方案一：使用RowNum函数
 ALTER PROCEDURE [dbo].[kuuy_P_CustomPaging]
 @getFields NVARCHAR(500)='', --返回字段
 @tblName NVARCHAR(255)='', --表名
 @strWhere NVARCHAR(1500)='', --查询条件
 @strOrder NVARCHAR(1000)='',--排序字段
 @IsCount BIT=0, --记录数，非零返回记录数
 @PageSize INT=10, --页尺寸
 @CurrentPage INT=1, --当前页码
 @ItemCount INT=0 OUTPUT
AS
 DECLARE @strSQL NVARCHAR(4000) --主语句
 DEClARE @strTmp NVARCHAR(2000) --临时变量
 DECLARE @ItemCountValue INT
 DECLARE @strCount NVARCHAR(4000) --查总的记录数
 
 IF (@strOrder !='')
  SET @strOrder = ' ORDER BY '+@strOrder
 ELSE
  SET @strOrder = ' ORDER BY ID'
 IF(@strWhere !='')
  SET @strWhere =' WHERE '+@strWhere
 
    SET @strTmp='WITH T_Orders AS(SELECT ROW_NUMBER() OVER('+@strOrder+') AS RowNum,'+@getFields+' FROM '+@tblName+@strWhere+')'
 IF (@IsCount!=0)
  SET @strCount = ' SET @ItemCount=(SELECT COUNT(*) FROM '+@tblName+@strWhere+')'
 ELSE
  SET @strCount =''
 
 SET @strSQL = @strTmp+'SELECT TOP ('+str(@PageSize)+') RowNum,'+@getFields+' '+
        'FROM T_Orders '+
        'WHERE RowNum BETWEEN '+str((@CurrentPage-1)*@PageSize+1)+' AND '+ str(@CurrentPage*@PageSize)+@strCount
       
 EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',@ItemCount=@ItemCountValue OUTPUT
 SET @ItemCount = @ItemCountValue
 RETURN
方案二：不使用ROWNUM()
ALTER PROCEDURE [dbo].[kuuy_P_CustomPaging_WithoutRowNum]
    @getFields NVARCHAR(500)='', --Columns_Name
    @tblName NVARCHAR(255)='', --Table_Name
    @strWhere NVARCHAR(1500)='', --Conditons
    @strOrder NVARCHAR(1000)='',--Order
    @keyName NVARCHAR(50)='ID', --KeyName
    @oprateKeyValue VARCHAR(2)='>', --Oprate
    @funKeyValue NVARCHAR(100)='MAX(ID)', --Identity The Order Key value
    @IsCount BIT=0, --Identity Whether Return The Total Results
    @PageSize INT=10, --RowsCount You Want Fetch Onece
    @CurrentPage INT=1, --CurrentPage
    @ItemCount INT=0 OUTPUT
AS
    DECLARE @strSQL NVARCHAR(4000) --Dynamic SQL string
    DEClARE @strTmp NVARCHAR(2000) --TEMP SQL String
    DECLARE @ItemCountValue INT
    DECLARE @strCount NVARCHAR(4000) --Total Records
   
    IF (@strOrder <> '')
        SET @strOrder = ' ORDER BY '+@strOrder
    ELSE
        SET @strOrder = ' ORDER BY ID'
    IF(@strWhere <> '')
    BEGIN
        IF(@CurrentPage > 1)
            SET @strWhere =' AND '+@strWhere
        ELSE
            SET @strWhere =' WHERE '+@strWhere
    END
      
    IF (@IsCount <> 0)
        SET @strCount = ' SET @ItemCount=(SELECT COUNT(*) FROM '+@tblName+@strWhere+')'
    ELSE
        SET @strCount =''
       
    IF(@CurrentPage > 1)
    BEGIN
           SET @strTmp='WITH T_Orders AS(SELECT TOP('+str(@CurrentPage*@PageSize)+')'+@keyName+' FROM '+@tblName+@strWhere+@strOrder+')'
           SET @strSQL = @strTmp+'SELECT TOP ('+str(@PageSize)+')'+@getFields+' '+
                          'FROM '+@tblName + ' '+
                          'WHERE '+ @keyName + @oprateKeyValue +
                          '(SELECT ' + @funKeyValue + ' FROM (SELECT TOP(' + str((@CurrentPage-1)*@PageSize) + ')* FROM T_Orders) AS T)'+
                        @strWhere+@strOrder
    END
       ELSE
           SET @strSQL = 'SELECT TOP('+str(@PageSize)+')'+@getFields+' FROM '+@tblName+@strWhere+@strOrder
                         
    EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',@ItemCount=@ItemCountValue OUTPUT
    PRINT @strSQL
    SET @ItemCount = @ItemCountValue
    RETURN