高效分页存储过程
Code
USE [Shenyi]
GO
/****** 对象: StoredProcedure [dbo].[UP_PageList] 脚本日期: 11/13/2008 17:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[UP_PageList]
(
@tblName VARCHAR(255)='', --表名
@GetFields VARCHAR(1000)='*', --需要返回的列
@OrderFldName VARCHAR(255), --排序的字段名,需要建立聚合索引提高效率
@fldName VARCHAR(255), --主键字段或其他建立索引字段
@PageSize INT=10, --页面尺寸
@PageIndex INT=1, --页码
@docount INT=0, --是否返回记录总数0不返回,1返回
@OrderType BIT=1, --设置排序类型0升序,1降序
@strWhere VARCHAR(max)='' --查询条件(不要加where)
)
AS
DECLARE @SqlMain VARCHAR(5000)--主SQL语句
DECLARE @Temp VARCHAR(100) --临时变量
DECLARE @Order VARCHAR(400) -- 排序类型
--------------------------------------
--如果DoReturnCount为就返回记录总数--
--------------------------------------
IF @docount = 1
BEGIN
SET @SqlMain = 'SELECT COUNT(1) AS Total FROM [' + @tblName + ']'
IF @strWhere !=''
SET @SqlMain = @SqlMain + ' WHERE ' + @strWhere
END
-------------------------------------------------------------------------------------------------
ELSE
BEGIN
-------------------------------------
--根据排序规则设置@Temp与@Order变量--
-------------------------------------
IF @OrderType = 1
BEGIN
SET @Temp = '<(SELECT MIN'
SET @Order = ' ORDER BY [' + @OrderFldName + '] DESC'
END
ELSE
BEGIN
SET @Temp = '>(SELECT MAX'
SET @Order =' ORDER BY [' + @OrderFldName + '] ASC'
END
-----------------------------------------------------------------------
SET @SqlMain = 'SELECT TOP ' + STR(@PageSize) + ' ' + @GetFields + '
FROM [' + @tblName + ']'
------------------------------------------
--如果是获取第一页就执行以下代码提高效率--
------------------------------------------
IF @PageIndex = 1
BEGIN
IF @strWhere != ''
SET @SqlMain = @SqlMain + ' WHERE ' + @strWhere
SET @SqlMain = @SqlMain + @Order
END
-------------------------------------------------------------------------------------
ELSE
---------------------------------------------------------------
--------------------------SQL语句主体--------------------------
---------------------------------------------------------------
BEGIN
SET @SqlMain = @SqlMain + ' WHERE [' + @OrderFldName + '] ' + @Temp + '
([' + @OrderFldName + ']) FROM ( SELECT TOP ' + STR((@PageIndex - 1) * @PageSize) + '
[' + @OrderFldName + '] FROM [' + @tblName + ']'
IF @strWhere != ''
SET @SqlMain = @SqlMain + ' WHERE ' + @strWhere + ' ' + @Order + ') AS TableTemp) AND ' + @strWhere + ' ' + @Order
ELSE
SET @SqlMain = @SqlMain + @Order + ') AS TableTemp)' + @Order
END
END
EXEC(@SqlMain)
高效的分页存储过程
如果山不向我走来,我就向山走去!
![](http://www.cnblogs.com/Emoticons/baimantou/202015214.gif)