通用数据库存储过程代码--高效分页存储过程

-- 获取指定页的数据 

CREATE PROCEDURE pagination

@tblName varchar(255), -- 表名 

@strGetFields varchar(1000) = '*', -- 需要返回的列 

@fldName varchar(255)='', -- 排序的字段名 

@PageSize int = 10, -- 页尺寸 

@PageIndex int = 1, -- 页码 

@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 

@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) 

AS 

declare @strSQL varchar(5000) -- 主语句 

declare @strTmp varchar(110) -- 临时变量 

declare @strOrder varchar(400) -- 排序类型 

if @doCount != 0 

begin 

if @strWhere !='' 

set @strSQL = "select count(*) as Total from [" + @tblName + "] where "[email protected] 

else 

set @strSQL = "select count(*) as Total from [" + @tblName + "]" 

end 

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况 

else 

begin 

if @OrderType != 0 

begin 

set @strTmp = "<(select min" 

set @strOrder = " order by [" + @fldName +"] desc" 

--如果@OrderType不是0，就执行降序，这句很重要！ 

end 

else 

begin 

set @strTmp = ">(select max" 

set @strOrder = " order by [" + @fldName +"] asc" 

end 

if @PageIndex = 1 

begin 

if @strWhere != '' 

set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder 

else 

set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ " from ["+ @tblName + "] "+ @strOrder 

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

end 

else 

begin 

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

set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ " from [" 

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder 

if @strWhere != '' 

set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ " from [" 

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "([" 

+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" 

+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " " 

+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder 

end 

end 

exec (@strSQL)

GO

**  中国无忧商务网千万数量级分页存储过程                     ** 

*************************************************************** 

参数说明: 

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 ) 

欢迎试用，有什么心得请 Mail:[email protected] 

示例代码 

 http://www.cn5135.com/Opportunities/Pagination.asp  ;

注：转载请保留相关版权说明，谢谢！^_^ 

***************************************************************/ 

( 

@Tables varchar(1000), 

@PrimaryKey varchar(100), 

@Sort varchar(200) = NULL, 

@CurrentPage int = 1, 

@PageSize int = 10, 

@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

现使用的：

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

------------------------------------

--用途：分页存储过程(对有主键的表效率极高)  

--说明：

------------------------------------

ALTER PROCEDURE [dbo].[UP_GetRecordByPage]

    @tblName      varchar(255),       -- 表名

    @fldName      varchar(255),       -- 主键字段名

    @PageSize     int = 10,           -- 页尺寸

    @PageIndex    int = 1,            -- 页码

    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回

    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序

    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(6000)       -- 主语句

declare @strTmp   varchar(100)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型

if @OrderType != 0

begin

    set @strTmp = '<(select min'

    set @strOrder = ' order by [' + @fldName +'] desc'

end

else

begin

    set @strTmp = '>(select max'

    set @strOrder = ' order by [' + @fldName +'] asc'

end

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'

    + @strOrder

if @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 + ' ' + @strOrder

if @PageIndex = 1

begin

    set @strTmp =''

    if @strWhere != ''

        set @strTmp = ' where ' + @strWhere

    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

        + @tblName + ']' + @strTmp + ' ' + @strOrder

end

if @IsReCount != 0

    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere

exec (@strSQL)