千万级分页高效存储过程.doc

/* Object: StoredProcedure dbo.GetRecordFromPageScript Date: 07/23/2008 18:42:05 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*函数名称: GetRecordFromPage函数功能: 获取指定页的数据参数说明: tblName 包含数据的表名fldName 关键字段名PageSize 每页记录数PageIndex 要获取的页码OrderType 排序类型, 0 - 升序, 1 - 降序strWhere 查询条件 (注意: 不要加 where)*/ALTER PROCEDURE dbo.GetRecordFromPagetblName varchar(255), - 表名fldName varchar(255), - 字段名PageSize int = 10, - 页尺寸PageIndex int = 1, - 页码OrderType bit = 0, - 设置排序类型, 非 0 值则降序strWhere varchar(2000) = - 查询条件 (注意: 不要加 where)ASdeclare strSQL varchar(6000) - 主语句declare strTmp varchar(1000) - 临时变量declare strOrder varchar(500) - 排序类型if OrderType != 0beginset strTmp = (select maxset strOrder = order by + fldName + ascendset strSQL = select top + str(PageSize) + * from + tblName + where + fldName + + strTmp + (+ fldName + ) from (select top + str(PageIndex-1)*PageSize) + + fldName + from + tblName + + strOrder + ) as tblTmp)+ strOrderif strWhere != set strSQL = select top + str(PageSize) + * from + tblName + where + fldName + + strTmp + (+ fldName + ) from (select top + str(PageIndex-1)*PageSize) + + fldName + from + tblName + where + strWhere + + strOrder + ) as tblTmp) and + strWhere + + strOrderif PageIndex = 1beginset strTmp = if strWhere != set strTmp = where ( + strWhere + )set strSQL = select top + str(PageSize) + * from + tblName + + strTmp + + strOrderendexec (strSQL)-/* Object: StoredProcedure dbo.usp_GetRecordFromPage Script Date: 07/23/2008 18:42:37 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.usp_GetRecordFromPagetblName varchar(1000), - 表名SelectFieldName varchar(4000), - 要显示的字段名(不要加select)strWhere varchar(4000), - 查询条件(注意: 不要加 where)OrderFieldName varchar(255), - 排序索引字段名PageSize int , - 页大小PageIndex int = 1, - 页码iRowCount int output, - 返回记录总数OrderType bit = 0 - 设置排序类型, 非 0 值则降序ASdeclare strSQL varchar(4000) - 主语句declare strTmp varchar(4000) - 临时变量declare strOrder varchar(400) - 排序类型declare strRowCount nvarchar(4000) - 用于查询记录总数的语句set OrderFieldName=ltrim(rtrim(OrderFieldName)if OrderType != 0beginset strTmp = (select maxset strOrder = order by + OrderFieldName + ascendset strSQL = select top + str(PageSize) + SelectFieldName+ from + tblName + where + OrderFieldName + strTmp + (+ right(OrderFieldName,len(OrderFieldName)-charindex(.,OrderFieldName) + ) from (select top + str(PageIndex-1)*PageSize)+ OrderFieldName + from + tblName + strOrder + ) as tblTmp)+ strOrderif strWhere != set strSQL = select top + str(PageSize) + SelectFieldName+ from + tblName + where + OrderFieldName + strTmp + (+ right(OrderFieldName,len(OrderFieldName)-charindex(.,OrderFieldName) + ) from (select top + str(PageIndex-1)*PageSize)+ OrderFieldName + from + tblName + where + strWhere + + strOrder + ) as tblTmp) and + strWhere + + strOrderif PageIndex = 1beginset strTmp = if strWhere != set strTmp = where + strWhereset strSQL = select top + str(PageSize) + SelectFieldName+ from + tblName + strTmp + + strOrderendexec(strSQL)if strWhere!=beginset strRowCount = select iRowCount=count(*) from + t