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

╰+攻爆jí腚メ 2022-03-28 15:38 324阅读 0赞

/\*  
名称: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+'][SQL]) T WHERE [T.'+@ID+'][T._ID] NOT IN  
(SELECT TOP '+CAST((@RecsPerPage\*(@Page-1)) AS VARCHAR(20))+' ['+@ID+'][SQL] FROM (['+@SQL+'][SQL]) T9 ORDER BY ['+@Sort+'][SQL]) ORDER BY ['+@Sort][SQL]

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)

[SQL]: http://www.v155.com/bbs/mailt'%20rel=
[T._ID]: http://www.v155.com/bbs/mailtT.'%20rel=