由于数据庞大,往往有多于 数以万计的纪录。只取当前页的数据是十分的必要。
根据我的实际情况,我设计了一下存储过程,也是因为数据库是由我设计的。
因此我就往容易实现的一方面去考虑。
1、数据表中必须有唯一自增标识 ID 字段,(这样的好处是明显的,可提高速度)
2、考虑到排序的问题,只允许按给定的字段排序。比如按col1,col2按升序或降徐,而不能先按col1升序然后按col2降序。一般用到的都是单个字段的排序。
/************************************************
存储过程名:获取当前页的记录集
说明:表字段必须有自增标识符 ID
Author:Region
MSN:hl_net@msn.com
参数:
@TableName :表名
@FieldName :显示的字段,如 “COL1,COL2" .若全部显示 ''或*,默认值为''
@OrderType :整型,排序 0 表示原数据顺序,1表示升序,2表示降序,默认值为0,
@OrderField:用于排序的字段,根据@OrderType排序,如“COL1,COL2”,默认值为''
@Condition:查询条件,不要有WHERE关键字,空则给'',默认值为''
@PageSize:一页的记录数,默认值为10
@PageIndex:要取的页索引,默认值为1
@PageCount:输出参数,返回总页数
实例:
DECLARE @PageCount INT
EXEC DIMS_GetCurrentPageRecord 'DIMS_TAF001','',1,'','',20,4,@PageCount OUTPUT
SELECT @PageCount
*************************************************/
CREATE PROCEDURE DIMS_GetCurrentPageRecord
@TableName VARCHAR(100),
@FieldName VARCHAR(200)='',
@OrderType INT = 0,
@OrderField VARCHAR(200)='',
@Condition VARCHAR(1000)='',
@PageSize INT = 10,
@PageIndex INT = 1,
@PageCount INT OUTPUT
AS
BEGIN
DECLARE @AscOrderStr VARCHAR(200)
DECLARE @DescOrderStr VARCHAR(200)
DECLARE @ConditionStr VARCHAR(1000)
DECLARE @FieldNameStr VARCHAR(200)
DECLARE @MaxRowIndex INT
DECLARE @GetRowsCount INT
DECLARE @SelectStr VARCHAR(3000)
-- 构造排序字符串 , 正序与反序
--------------------------------------------
IF(@OrderType = 0)
BEGIN
SET @AscOrderStr = ' ID '
SET @DescOrderStr = ' ID DESC '
END
ELSE IF(@OrderType = 1)
BEGIN
IF(@OrderField ='')
BEGIN
SET @AscOrderStr = ' ID '
SET @DescOrderStr = ' ID DESC '
END
ELSE
BEGIN
SET @AscOrderStr = @OrderField;
SET @DescOrderStr = REPLACE(@OrderField,',',' DESC, ') + ' DESC '
END
END
ELSE IF(@OrderType = 2)
BEGIN
IF(@OrderField ='')
BEGIN
SET @DescOrderStr = ' ID '
SET @AscOrderStr = ' ID DESC '
END
ELSE
BEGIN
SET @DescOrderStr = @OrderField;
SET @AscOrderStr = REPLACE(@OrderField,',',' DESC, ') + ' DESC '
END
END
IF(@AscOrderStr != '')
SET @AscOrderStr = ' ORDER BY ' + @AscOrderStr
IF(@DescOrderStr != '')
SET @DescOrderStr = ' ORDER BY ' + @DescOrderStr
-- 构造条件语句,最大行数,显示字段,获取的行数
---------------------------------------------
IF(@Condition != '')
SET @ConditionStr = ' WHERE ' + @Condition
ELSE
SET @ConditionStr = @Condition
SET @MaxRowIndex = @PageSize*@PageIndex
IF(@FieldName = '' OR @FieldName = '*')
SET @FieldNameStr = ' * '
ELSE
SET @FieldNameStr = ' '+@FieldName+' '
DECLARE @TableRowsCount INT
CREATE TABLE #TB ( RounCount INT)
INSERT INTO #TB EXEC ('SELECT COUNT(*) FROM '+@TableName)
SELECT @TableRowsCount = RounCount FROM #TB
DROP TABLE #TB
IF(@TableRowsCount<@MaxRowIndex )
BEGIN
SET @GetRowsCount = @PageSize -(@MaxRowIndex - @TableRowsCount)
IF(@GetRowsCount<0)
SET @GetRowsCount = 0
END
ELSE
SET @GetRowsCount = @PageSize
--计算总页数
IF(@TableRowsCount = 0)
SET @PageCount = 1
ELSE
BEGIN
SET @PageCount = CEILING(@TableRowsCount/@PageSize)
END
-- 构造查询语句,执行语句
---------------------------------------------
SET @SelectStr = 'SELECT TOP '+ CAST(@GetRowsCount AS VARCHAR) +@FieldNameStr+' FROM (
SELECT TOP '+ CAST(@GetRowsCount AS VARCHAR) +' * FROM (SELECT TOP
'+ CAST(@GetRowsCount AS VARCHAR) +' * FROM (SELECT TOP '+
CAST(@MaxRowIndex AS VARCHAR)+' * FROM '+
@TableName+@ConditionStr+@AscOrderStr+') DERIVEDTBL' + @DescOrderStr+
') DERIVEDTBL' + @AscOrderStr+') DERIVEDTBL '
--PRINT @SelectStr
EXEC (@SelectStr)
END
GO