通用存储过程.分页存储过程(转)

通用存储过程.分页存储过程 

/*

名称:spAll_ReturnRows

输入:

输出:

调用:

  EXEC spAll_ReturnRows 'SELECT  * FROM 表名', 页号, 返回记录数, '主键', '排序字段'

  spAll_ReturnRows 'SELECT  * FROM all_Categories',2,10,'[ID]','[ID]'

说明:[百万级]通用存储过程.分页存储过程..返回指定返回条数、指定页数的记录



*/



CREATE PROCEDURE dbo.spAll_ReturnRows

(

@SQL nVARCHAR(4000),

@Page int,

@RecsPerPage int,

@ID VARCHAR(255),

@Sort VARCHAR(255)

)

AS



DECLARE @Str nVARCHAR(4000)



SET @Str='SELECT   TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN 

(SELECT   TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort



PRINT @Str



EXEC sp_ExecuteSql @Str

GO



/*

名称:spAll_DeleteNoneUnique

输入:要查询的表名和字段列表

输出:

调用:

说明:实现千万级数据的分页显示!--可以在5秒内获取1448万条记录里的第1200页的100条记录，雄不？

*/



CREATE PROCEDURE GetRecordFromPage

    @tblName      varchar(255),       -- 表名

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

    @PageSize     int = 10,           -- 页尺寸

    @PageIndex    int = 1,            -- 页码

    @IsCount      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 @IsCount != 0

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



exec (@strSQL)



GO

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

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

--说明：

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



CREATE 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)





GO