实现千万级数据的分页的通用存储过程

01 set ANSI_NULLS ON

02 set QUOTED_IDENTIFIER ON

03 go

04

05 /*

06 功能描述： 通用分页显示查询

07 如果有自增标识字段，在@strGetFields中不要加入此字段信息，

08 如果非要加入的话，要 (fldName + 0) AS fldName 这样处理；

09 输入参数：

10 @tblName: 表名

11 @strGetFields: 需要返回的列 '*':返回所以列信息

12 @PageSize: 页尺寸

13 @PageIndex: 页码

14 @doCount: 返回记录总数, 非 0 值则返回

15 @strOrderBy: 排序字段信息,(注意: 不要加 ORDER BY)

16 格式: Field1 DESC, Field2 ASC

17 @strWhere: 查询条件,(注意: 不要加 WHERE)

18 输出参数： @RecordCount: 记录总数

19 作 者： Nestcn

20 创建时间： 2010-03-09

21 更改纪录：

22 */

23 ALTER PROCEDURE [dbo].[MyPagination]

24 (

25 @tblName varchar(255),

26 @strGetFields varchar(1000) = '*',

27 @PageSize int = 10,

28 @PageIndex int = 1,

29 @doCount bit = 0,

30 @strOrderBy varchar(500) = '',

31 @strWhere varchar(1500) = '',

32 @RecordCount int output

33 )

34 AS

35 -- 主语句

36 DECLARE @strSQL varchar(5000) SET @strSQL = ''

37 -- 排序变量

38 DECLARE @strOrder varchar(400) SET @strOrder = ''

39

40 SET @RecordCount = 0

41 --如果@doCount传递过来的不是0，就执行总数统计

42 IF (@doCount != 0)

43 BEGIN

44 DECLARE @sWhere varchar(2000)

45

46 SET @sWhere = ''

47 IF (@strWhere != '')

48 SET @sWhere = ' WHERE ' + @strWhere

49

50 SET @strSQL = 'if exists (select * from dbo.sysobjects where id = object_id(''[dbo].[tmpTable]'') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '

51 SET @strSQL = @strSQL + ' UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM [' + @tblName + '] ' + @sWhere + ') '

52 SET @strSQL = @strSQL + ' ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM [' + @tblName + '] ' + @sWhere

53

54 EXEC (@strSQL)

55

56 SELECT @RecordCount=Total FROM tmpTable

57

58 --删除总数统计临时表

59 EXEC ('DROP TABLE tmpTable')

60 END

61

62 PRINT @RecordCount

63

64 --排序字段信息

65 IF (@strOrderBy != '')

66 SET @strOrder = ' ORDER BY ' + @strOrderBy

67 --如果是第一页就执行以上代码，这样会加快执行速度

68 IF (@PageIndex = 1)

69 BEGIN

70 IF (@strWhere != '')

71 SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder

72 ELSE

73 SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM ['+ @tblName + '] '+ @strOrder

74 END

75 ELSE

76 BEGIN

77 --为搜索表建立自动编号 保存到临时表中

78 SET @strSQL = 'SELECT TOP ' + str(@PageIndex*@PageSize) + ' IDENTITY(int,1,1) AS IID, ' + @strGetFields + ' INTO #tmpTable FROM [' + @tblName + ']'

79 IF (@strWhere != '')

80 SET @strSQL = @strSQL + ' WHERE ' + @strWhere + @strOrder

81 ELSE

82 SET @strSQL = @strSQL + @strOrder

83

84 --以下代码赋予了@strSQL以真正执行的SQL代码

85 SET @strSQL = @strSQL + ' SELECT ' + @strGetFields + ' FROM #tmpTable WHERE IID > ' + str((@PageIndex-1)*@PageSize) + ' DROP TABLE #tmpTable'

86 END

87

88 PRINT @strSQL

89

90 --执行分页查询

91 EXEC (@strSQL)