Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        苏飞
-- Create date: 20090425
-- Description:    查询所有的用户信息
-- =============================================
ALTER PROCEDURE [dbo].[UIS_selectAllUsers]
@startIndex INT, --开始页
@pageSize INT --每页的行数
AS
BEGIN
with SClass as(
select row_number() over(order by ID) as rownumber,*
from  dbo.UsersInfo where  User_QX!=5)
/**上半句产生一个临时表，表名为Small_Class,其内容来自于自查询select row_number().**/
select ID,UserName,User_QX,TrueName,Sex,Contact,RegisterTime
from SClass
where rownumber >=(@startindex-1)*@pagesize+1 and rownumber<=(@startindex-1)*@pagesize+(@pagesize)
select count(*) from  dbo.UsersInfo where  User_QX!=5
END

Code
CREATE PROCEDURE dbo.pager_Sybase
/* [ (@param_name datatype [= default] [output] ),  ] */
@PageIndex int,
@PageSize int,
@sql varchar(2000),
@SqlTable varchar(500),
@SqlWhere varchar(1000),
@RowCount int output
WITH RECOMPILE
AS
    Declare @sqlCount varchar(1000), @tmp_sql varchar(3000), @LowerBand numeric(8), @UpperBand numeric(8)

    /* ************** 取得符合查詢條件的数据行总笔数(Total Row Count)，用來顯示位於 GridView 下方的「页码行」的「总笔数」 ************** */
    /* SET @RowCount = (SELECT COUNT(*) FROM 資料表)
       因 Sybase 的 Stored Procedure 功能有限，無法像 SQL Server 一樣用上述語法，因此多建立一個 Temp Table，當作中介、暫存数据之用
    */
    CREATE TABLE #tmp1 (cnt numeric(10)) 
    SELECT @sqlCount ='INSERT INTO #tmp1 SELECT COUNT(*) FROM ' + @SqlTable  + ' WHERE ' + @SqlWhere
    EXEC (@sqlCount)
    SET @RowCount = (SELECT cnt FROM #tmp1)


    /* ************** 以下為實際要傳回前端 GridView 的 DataTable 內容 **************** */    

    -- 計算要顯示的数据行的起、迄数据行索引
    -- 計算 @LowerCount (起始撈取的第一筆的数据行索引)、@UpperCount (要撈取的最後一筆的数据行索引)
    -- @PageIndex : 要顯示的這一頁的頁面索引。配合 .NET 定義 : 第一頁的索引編號為 0，依序加 1，依此類推
    if (@PageIndex = 0)     --若為第一頁
    begin
        SET @LowerBand  = 0
        SET @UpperBand  = @PageSize 
    end
    else
    begin
        SET @LowerBand  = @PageIndex * @PageSize 
        SET @UpperBand  = (@PageIndex + 1) * @PageSize 
    end

    -- 一組動態 sql (@tmp_sql)
    -- @sql 為實際從 DAL2 丟進來的完整 SELECT SQL 语句
    -- SET ROWCOUNT 功能，類似於 SELECT TOP n

    SELECT @tmp_sql = ' SET ROWCOUNT ' + rtrim(ltrim(convert(char, @UpperBand))) + ' '
    SELECT @tmp_sql = @tmp_sql + ltrim(@sql) 

    -- 若目前所在頁數不是第一頁，則再刪除不需要的数据行(@LowerBand)
    If @LowerBand > 0  
    Begin 
        SELECT @tmp_sql = @tmp_sql + ' SET ROWCOUNT ' + rtrim(ltrim(convert(char, @LowerBand))) + ' DELETE FROM #tmp2 '
    End 
    
    -- SET ROWCOUNT 0 表示資料表「全選」
    SELECT @tmp_sql = @tmp_sql + ' SET ROWCOUNT 0 ' 
    SELECT @tmp_sql = @tmp_sql + ' SELECT * FROM #tmp2'

    EXEC(@tmp_sql)

    RETURN

Code
USE [DWSParameter]
GO
/****** 物件:  StoredProcedure [dbo].[pager_SqlServer2005]    指令碼日期: 11/03/2008 15:04:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pager_SqlServer2005]
@StartRowIndex    int,
@PageSize int,
@tableName nvarchar(3000),
@columnName nvarchar(2000),
@sqlWhere nvarchar(1000),
@groupBy nvarchar(100),
@orderBy nvarchar(1000),
@rowCount int output
WITH RECOMPILE
AS    
    Declare @sqlCount nvarchar(1000), @sqlDataTable nvarchar(3000), @LowerBand int, @UpperBand int 

/* *** 取得符合查詢條件的数据行总笔数(Total Row Count)，用來顯示位於 GridView 下方的「数据行」的「总笔数」 *** */
if @groupBy IS NULL
begin
    SET @sqlCount='SELECT @rowCount=COUNT(*) FROM ' + @tableName + ' WHERE ' + @sqlWhere   
end 
else
begin
    SET @sqlCount='SELECT @rowCount=COUNT(*) FROM (SELECT ' + @columnName + ' FROM ' + @tableName + 
                ' WHERE ' + @sqlWhere + ' GROUP BY ' + @groupBy + ') as temp0'
end

EXEC sp_executesql  @sqlCount,N'@rowCount int output',@rowCount output
    
    
-- 計算要顯示的数据行的起、迄数据行索引
-- 計算 @LowerCount (起始撈取的第一筆的数据行索引)、@UpperCount (要撈取的最後一筆的数据行索引)
-- @StartRowIndex 数据行索引
    if (@StartRowIndex = 0)
    begin
        SET @LowerBand  = 0
        SET @UpperBand  = @PageSize 
    end
    else
    begin
        SET @LowerBand  = @StartRowIndex + 1
        SET @UpperBand  = @StartRowIndex + @PageSize 
    end

/* *** 以下為實際要傳回前端 GridView 的 DataTable 內容 *** */
if @groupBy IS NULL
begin
    SET @sqlDataTable = 'WITH tempTable AS
    (SELECT ' + @columnName + ', ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS tempRowNum
    FROM ' + @tableName + ' WHERE ' + @sqlWhere + ') 
    SELECT * FROM tempTable WHERE tempRowNum BETWEEN ' + cast(@LowerBand as nvarchar) + ' AND ' 
    + cast(@UpperBand as nvarchar) + ';'
end
else
begin
    SET @sqlDataTable = 'WITH tempTable AS
    (SELECT ' + @columnName + ', ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS tempRowNum
    FROM ' + @tableName + ' WHERE ' + @sqlWhere + ' GROUP BY ' + @groupBy + ') 
    SELECT * FROM tempTable WHERE tempRowNum BETWEEN ' + cast(@LowerBand as nvarchar) + ' AND ' 
    + cast(@UpperBand as nvarchar) + ';'
end

exec (@sqlDataTable)


RETURN