• 466 阅读
  • 1 回复

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

视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)

代码 复制 - 运行

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
/* 
功能描述: 通用分页显示查询 
如果有自增标识字段,在@strGetFields中不要加入此字段信息, 
如果非要加入的话,要 (fldName + 0) AS fldName 这样处理; 
输入参数: 
@tblName: 表名 
@strGetFields: 需要返回的列 '*':返回所以列信息 
@PageSize: 页尺寸 
@PageIndex: 页码 
@doCount: 返回记录总数, 非 0 值则返回 
@strOrderBy: 排序字段信息,(注意: 不要加 ORDER BY) 
格式: Field1 DESC, Field2 ASC 
@strWhere: 查询条件,(注意: 不要加 WHERE) 
输出参数: @RecordCount: 记录总数 
作 者: Nestcn 
创建时间: 2010-03-09 
更改纪录: 
*/
ALTER PROCEDURE [dbo].[MyPagination] 
( 
@tblName varchar(255), 
@strGetFields varchar(1000) = '*', 
@PageSize int = 10, 
@PageIndex int = 1, 
@doCount bit = 0, 
@strOrderBy varchar(500) = '', 
@strWhere varchar(1500) = '', 
@RecordCount int output
) 
AS
-- 主语句 
DECLARE @strSQL varchar(5000) SET @strSQL = ''
-- 排序变量 
DECLARE @strOrder varchar(400) SET @strOrder = ''
 
SET @RecordCount = 0 
--如果@doCount传递过来的不是0,就执行总数统计 
IF (@doCount != 0) 
BEGIN
DECLARE @sWhere varchar(2000) 
 
SET @sWhere = ''
IF (@strWhere != '') 
SET @sWhere = ' WHERE ' + @strWhere 
 
SET @strSQL = 'if exists (select * from dbo.sysobjects where id = object_id(''[dbo].[tmpTable]'') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '
SET @strSQL = @strSQL + ' UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM [' + @tblName + '] ' + @sWhere + ') '
SET @strSQL = @strSQL + ' ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM [' + @tblName + '] ' + @sWhere 
 
EXEC (@strSQL) 
 
SELECT @RecordCount=Total FROM tmpTable 
 
--删除总数统计临时表 
EXEC ('DROP TABLE tmpTable') 
END
 
PRINT @RecordCount 
 
--排序字段信息 
IF (@strOrderBy != '') 
SET @strOrder = ' ORDER BY ' + @strOrderBy 
--如果是第一页就执行以上代码,这样会加快执行速度 
IF (@PageIndex = 1) 
BEGIN
IF (@strWhere != '') 
SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder 
ELSE
SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM ['+ @tblName + '] '+ @strOrder 
END
ELSE
BEGIN
--为搜索表建立自动编号 保存到临时表中 
SET @strSQL = 'SELECT TOP ' + str(@PageIndex*@PageSize) + ' IDENTITY(int,1,1) AS IID, ' + @strGetFields + ' INTO #tmpTable FROM [' + @tblName + ']'
IF (@strWhere != '') 
SET @strSQL = @strSQL + ' WHERE ' + @strWhere + @strOrder 
ELSE
SET @strSQL = @strSQL + @strOrder 
 
--以下代码赋予了@strSQL以真正执行的SQL代码 
SET @strSQL = @strSQL + ' SELECT ' + @strGetFields + ' FROM #tmpTable WHERE IID > ' + str((@PageIndex-1)*@PageSize) + ' DROP TABLE #tmpTable'
END
 
PRINT @strSQL 
 
--执行分页查询 
EXEC (@strSQL) 



http://www.cnblogs.com/caojinqin/archive/2010/03/09/1681544.html
小鱼的淘宝店铺-多多支持哇

一个支持千万级分页的sql存储过程

视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)

代码 复制 - 运行

use pubs;
go 



Create PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where 
7.Group :Group语句,不带Group By
效果演示:http://www.cn5135.com/_App/Enterprise/QueryResult.aspx
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int,
@PageSize int,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
Select @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' Where ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
'
)
GO

/*如果修改了存储过程,先删除了,再执行*/
DROP PROCEDURE CN5135_SP_Pagination;
/*调用示例*/
execute  CN5135_SP_Pagination @Tables= authors,@PrimaryKey=au_id,@CurrentPage=2,@PageSize=8;

小鱼的淘宝店铺-多多支持哇