俺写的 万能存储过程
CREATE PROCEDURE upPaging
@PageIndex INT,
@PageSize INT,
@tableName varchar(30), ---表名称
@colNames varchar(50), --查询表的列
@orderby varchar(50), --排序规则
@oredrType varchar(10) = 'ASC' --排序是降序，还是升序
AS
DECLARE @StartData INT
DECLARE @EndData INT
SET @StartData = (@PageIndex-1)*@PageSize+1
SET @EndData=@PageSize*@PageIndex

DECLARE @strSQL VARCHAR(1000)
SET @strSQL='SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY '+@orderby+' '+@oredrType+') AS A,'+@colNames+' FROM '+@tableName+') AS P WHERE A
BETWEEN '+STR(@StartData)+' AND '+STR(@EndData)+''
PRINT (@strSQL)
EXEC (@strSQL)

EXEC upPaging 1,5,Student,[*],id,[DESC]

[解决办法]

SQL code

CREATE PROCEDURE [dbo].[sp_common_paging]    @pageIndex INT = 1,    @pageSize INT = 10,    @tableName VARCHAR(256) = '',    @orderby VARCHAR(256) = '',    @fields VARCHAR(256) = '',    @condition NVARCHAR(MAX) = '',    @recordCount INT OUTPUTASBEGIN    IF @tableName IS NULL       OR @tableName = ''    BEGIN        RAISERROR('查询的数据表不为能空！', 16, 1)        RETURN    END        IF NOT EXISTS(           SELECT *           FROM   sys.tables t           WHERE  t.[name] = @tableName       )    BEGIN        RAISERROR('数据表不存在!', 16, 1)        RETURN    END        SELECT @tableName = QUOTENAME(@tableName)    IF @pageIndex IS NULL       OR @pageIndex = ''        SELECT @pageIndex = 1        IF @pageSize IS NULL       OR @pageSize = ''        SET @pageSize = 10        IF @fields = ''       OR @fields IS NULL        SET @fields = '*'        IF @orderby IS NULL       OR @orderby = ''    BEGIN        DECLARE @count SMALLINT        SELECT @count = COUNT(1)        FROM   sys.COLUMNS c               INNER JOIN sys.index_columns ic                    ON  c.[object_id] = ic.[object_id]                    AND c.column_id = ic.column_id               INNER JOIN sys.indexes i                    ON  c.[object_id] = i.[object_id]                    AND ic.index_id = i.index_id                    AND i.is_primary_key = '1'                    AND i.[object_id] = OBJECT_ID(@tableName)                IF @count > 0        BEGIN            DECLARE @fieldName  VARCHAR(256)            DECLARE #pkCursor   CURSOR STATIC             FOR                SELECT c.NAME                FROM   sys.COLUMNS c                       INNER JOIN sys.index_columns ic                            ON  c.[object_id] = ic.[object_id]                            AND c.column_id = ic.column_id                       INNER JOIN sys.indexes i                            ON  c.[object_id] = i.[object_id]                            AND ic.index_id = i.index_id                            AND i.is_primary_key = '1'                            AND i.[object_id] = OBJECT_ID(@tableName)                        OPEN #pkCursor             FETCH NEXT FROM #pkCursor INTO @fieldName            WHILE @@FETCH_STATUS = 0            BEGIN                IF (@orderby IS NULL OR @orderby = '')                    SET @orderby = '' + @fieldName                ELSE                    SET @orderby = @orderby + ',' + @fieldName                                FETCH NEXT FROM #pkCursor INTO @fieldName            END            CLOSE #pkCursor            DEALLOCATE #pkCursor        END        ELSE        BEGIN            SELECT @orderby = [NAME]            FROM   sys.[columns] c            WHERE  c.[object_id] = OBJECT_ID(@tableName)                   AND c.column_id = 1        END    END        PRINT(@orderby)    DECLARE @sql       NVARCHAR(MAX)    DECLARE @sqlCount  NVARCHAR(MAX)    IF @pageIndex = 1    BEGIN        SELECT @sql = 'select top(@pageSize) ' + @fields + ' from ' + @tableName         SET @sqlCount = 'select @recordCount=count(1) from ' + @tableName        IF @condition IS NOT NULL           AND @condition <> ''        BEGIN            SET @sql = @sql + ' where ' + @condition            SET @sqlCount = @sqlCount + ' where ' + @condition        END                IF @orderby IS NOT NULL           AND @orderby <> ''            SET @sql = @sql + ' order by ' + @orderby                EXEC sp_executesql @sql,             N'@pageSize int,@orderby nvarchar(max)',             @pageSize,             @orderby                EXEC sp_executesql @sqlCount,             N'@recordCount int out',             @recordCount OUT                RETURN    END    ELSE    BEGIN        IF @fields IS NULL           OR @fields = ''           OR @fields = '*'            SELECT @fields = dbo.getFields(@tableName)                SELECT @sql = 'select ' + @fields +               ' from (select row_number() over(order by @orderby) as rowIndex,'                + @fields + ' from ' + @tableName                SET @sqlCount = 'select @recordCount=count(1) from ' + @tableName        IF @condition IS NOT NULL           AND @condition <> ''        BEGIN            SET @sql = @sql + ' where ' + @condition            SET @sqlCount = @sqlCount + ' where ' + @condition        END                SET @sql = @sql +            ') t where rowIndex between ((@pageIndex-1)*@pageSize+1) and (@pageIndex*@pageSize)'                EXEC sp_executesql @sql,             N'@orderby varchar(max),@pageIndex int,@pageSize int',             @orderby,             @pageIndex,             @pageSize                EXEC sp_executesql @sqlCount,             N'@recordCount int out',             @recordCount OUT    ENDEND 
 
[解决办法]
说实话，万能这个实在不敢认同。
1、分页一般还要查询记录总数，当然你另外查询也可以，但直接在这里定义一个输出参数获取记录总数比较好；
2、搜索查询时一般需要WHERE条件，这里也没有；
3、多字段排序也不支持；
4、SQL2000不支持，只支持SQL2005以上版本
[解决办法]
REATE PROCEDURE upPaging
@PageIndex INT,
@PageSize INT,
@tableName varchar(30), ---表名称
@colNames varchar(50
[解决办法]
探讨

哦，已经使用了ROW_NUMBER()
不过，如果希望order by f1,f2 desc,f3,f4 desc
就不够用了

[解决办法]
探讨

引用:
哦，已经使用了ROW_NUMBER()
不过，如果希望order by f1,f2 desc,f3,f4 desc
就不够用了



SQL code

--只是拼接字符串，这个是可以实现的,例如
EXEC upPaging 1,5,'Student','*','f1,f2 desc,f3,f4','DESC'

[解决办法]
太友爱了，万能的程序员 啊！