MsSql 存储过程分页代码 [收集多篇]

--使用说明 本代码适用于MsSql2000,对于其它数据库也可用.但没必要
--创建存储过程
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = *, -- 需要返回的列
@fldName varchar(255)=, -- 排序的字段名(可包含如TABLE.FLDNAME形式）
@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) -- 排序类型
declare @fldName_t varchar(255) -- 在分页时用的排序字段名，不包含多表并列时的表名
set @fldName_t = right(@fldName,len(@fldName)-CHARINDEX(.,@fldName))
if @doCount != 0
begin
if @strWhere !=
set @strSQL = select count(*) as Total from + @tblName + where +@strWhere
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) + +@strGetFields+ from + @tblName + where + @strWhere + + @strOrder
else
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from + @tblName + + @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from + @tblName + where + @fldName + + @strTmp + (+ @fldName_t + ) from (select top + str((@PageIndex-1)*@PageSize) + + @fldName + from + @tblName + + @strOrder + ) as tblTmp)+ @strOrder
if @strWhere !=
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from + @tblName + where + @fldName + + @strTmp + (+ @fldName_t + ) from (select top + str((@PageIndex-1)*@PageSize) + + @fldName + from + @tblName + where + @strWhere + + @strOrder + ) as tblTmp) and + @strWhere + + @strOrder
end
end
exec (@strSQL)
go
--测试
create table news --建表
(
n_id int iDENTITY(1,1) primary key,
n_title char(200),
n_content text
)
--写循环插入1000000条的数据
create proc tt
as
declare @i int
set @i=0
while(@i<1000000)
begin
insert into news(n_title,n_content) values(sb,dsfsdfsd)
set @i=@i+1
end
exec tt
exec pagination news,*,n_id,1000,2,0,0,

自己改写的一个分页存储过程
CREATE PROC Paging
(
@pageSize int,
@pageIndex int,
@pageField nvarchar(32),
@countTotal bit=1,
@fieldQuery nvarchar(512),
@tableQuery nvarchar(512),
@whereQuery nvarchar(2048),
@orderQuery nvarchar(512)
)
AS
DECLARE @bdate Datetime
SET @bdate = getdate()
DECLARE @itemcount int
SET @itemcount=@pageIndex*@pageSize
DECLARE @itemlowwer int
SET @itemlowwer=(@pageIndex-1)*@pageSize
DECLARE @cmd nvarchar(3062)

IF @pageIndex=1
SET @cmd =SELECT TOP ‘+CAST(@pageSize AS NVARCHAR)+ ‘+@fieldQuery+ FROM ‘+@tableQuery+ WHERE ‘+@whereQuery+ ORDER BY ‘+@orderQuery
ELSE
SET @cmd=SELECT ‘+@fieldQuery+ FROM ‘+@tableQuery+ WHERE ‘+@pageField+ IN (SELECT TOP ‘+CAST(@itemcount as nvarchar)+ ‘+@pageField+ FROM ‘+@tableQuery+ WHERE ‘+@whereQuery+ ORDER BY ‘+ @orderQuery+)
AND ‘+@pageField+ NOT IN (SELECT TOP ‘ +CAST(@itemlowwer as nvarchar)+ ‘+@pageField+ FROM ‘+@tableQuery+ WHERE ‘+@whereQuery+ ORDER BY ‘+ @orderQuery+)
–print @cmd
EXEC(@cmd)

SELECT DATEDIFF( ms , @bdate , getdate() )

IF @countTotal =1
BEGIN
SET @cmd = ‘SELECT COUNT( 0) FROM ‘+@tableQuery+ WHERE ‘+@whereQuery
EXEC(@cmd)
END
GO