分页存储过程

分页存储过程

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法一：

CREATE PROCEDURE sp_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO


－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法二：

CREATE PROCEDURE page_list_test
@TBname VarChar(50), --表名
@pagesize int, --每页大小
@pageindex int, --页索引，从1开始
@docount bit, --是否获取记录数，1是;0不是
@name VarChar(50), --字段值
@clound VarChar(50) --字段名
as
set nocount on
declare @sql nvarchar(4000)

if(@docount=1)
set @sql='select count(*) from '+@TBname
+' where '+@clound+'='+''''+@name+''''
else
begin
if @pageindex=1
set @sql='select top '+convert(varchar,@pagesize)+' * from '+@TBname
+' where' +' '+@clound+'='+''''+@name+''''
+' order by id desc'
else
begin
set @sql='select top '+convert(varchar,@pagesize)+' * from '+@TBname
+' where' +' '+@clound+'='+''''+@name+''''
+' and id<(select min(id) from(select top '+convert(varchar,(@pageindex-1)*@pagesize)+' id from '+@TBname
+' where' +' '+@clound+'='+''''+@name+''''
+' order by id desc)t)'
+' order by id desc'
end
end

exec(@sql)
set nocount off
GO

hnsmxzb 发表于 2006-1-7 2:01:18
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法三：


ALTER PROCEDURE dbo.GetPagingRecord
(
@tablename varchar(100),--表名或视图表
@fieldlist varchar(4000)='*',--欲选择字段列表
@orderfield varchar(100),--排序字段
@keyfield varchar(100),--主键
@pageindex int,--页号,从0开始
@pagesize int=20,--页尺寸
@strwhere varchar(4000),--条件
@ordertype bit=1--排序,1,降序,0,升序

)
AS
/**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
SET NOCOUNT ON

declare @sqlstr varchar(6000)
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr='declare @CurPageNum int;'
set @sqlstr=@sqlstr+'declare @nextpagenum int;'
set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (

select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'

order by '+@orderfield+' desc) order by '+@orderfield+' desc'';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (

select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'

order by '+@orderfield+' asc) order by '+@orderfield+' asc'';'
end
set @sqlstr=@sqlstr+'execute( @sqlstr)'
print @sqlstr
execute(@sqlstr)


//保证好用.

///*************************///
这两年来慢慢习惯了光说不练
///*************************///


--------------------------------------------------------------------------------------------------
方法四：
CREATE procedure Quest
@class int,--问题的类别
@pageSize int,--每页显示信息数
@currentPage int,--当前页数
@totalPage int output--页总数
as
declare @strQuery as varchar(1000) --查询语句
declare @rowCount as int --总行数
--根据条件查询总记录条数用于分页功能的信息显示
select @rowCount=count(ID) from (select ID from Question where Type=@class and Put_State=1 and Del=0) as temphelp
set @totalPage = ceiling(cast(@rowCount as float)/cast(@pageSize as float))--根据总条数来获取记录的总分页数
if @currentPage >1--判断如果当前页大于1就执行下一步
begin
--如果当前页大于总页数，那么就把当前页设为总页数
if @currentPage>@totalPage
begin
set @currentPage = @totalPage
end
--执行SQL查询取出要查询的记录
set @strQuery='SELECT TOP '+cast(@pageSize as varchar(10))+' ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Answer_Date,Handler FROM (select ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Handler from Question where Type='+cast(@class as varchar(10))+' and Put_State=1 and Del=0) as temphelp WHERE id NOT IN (SELECT TOP '+cast((@currentPage-1)*@pageSize as varchar(10))+' id FROM (select ID,Add_Date,Answer_Date from Question where Type='+cast(@class as varchar(10))+' and Put_State=1 and Del=0) as temphelp ORDER BY (Case ord When 1 Then 0 Else 1 End),Add_Date desc) ORDER BY (Case ord When 1 Then 0 Else 1 End),Add_Date desc'
end
else--如果当前页不大于1就直接执行查询略过判断获得记录
begin
set @strQuery ='select top '+cast(@pageSize as varchar(10))+' ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Answer_Date,Handler from (select ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Handler from Question where Type='+cast(@class as varchar(10))+' and Put_State=1 and Del=0) as temphelp ORDER BY (Case ord When 1 Then 0 Else 1 End),Add_Date desc'
end
exec (@strQuery)--执行语句
return
GO





－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法五：
create proc sp_PublicTurnPage(
@TBName nvarchar(2000)='',--表名,如 pinyin
@PageSizeint=10,--每页的记录数,默认为 10
@CurPageint=1,--表示当前页 1
@KeyFieldnvarchar(100)='ID',--关键字段名，默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDescnvarchar(4)='ASC',--关键字的升、降序，默认为升序 ASC , 降序为 DESC
@Fieldsnvarchar(2000)='*',--所选择的列名，默认为全选
@Conditionnvarchar(2000)='',--where 条件,默认为空
@Ordernvarchar(200)=''--排序条件,默认为空
)as
if @TBName = ''
begin
raiserror('请指定表名！',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror('当前页数和每页的记录数都必须大于零！',11,1)
return
end
if @KeyAscDesc = 'DESC'
set @KeyAscDesc = '<'
else
set @KeyAscDesc = '>'
if @Condition <> ''
set @Condition = ' where ' + @Condition
declare @SQL nvarchar(2000)

set @SQL = ''
if @CurPage = 1
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + ' ' + @Order
else
begin
declare @iTopNum int
set @iTopNum = @PageSize * (@CurPage - 1)
set @SQL = @SQL + 'declare @sLastValue nvarchar(100)' + char(13)
set @SQL = @SQL + 'SELECT Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue=' + @KeyField + ' FROM ' + @TBName + @Condition + ' ' + @Order + char(13)

declare @Condition2 nvarchar(200)
if @Condition = ''
set @Condition2 = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '
else
set @Condition2 = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + @Condition2 + @Order
end
EXECUTE sp_executesql @SQL
－－－－－
方法六：
CREATE proc page
@RecordCountint output,
@QueryStr nvarchar(100)='table1',--表名、视图名、查询语句
@PageSize int=20,--每页的大小(行数)
@PageCurrent int=2,--要显示的页 从0开始
@FdShow nvarchar (1000)='*',--要显示的字段列表
@IdentityStr nvarchar (100)='id',--主键
@WhereStr nvarchar (200)='1=1',
@FdOrder nvarchar(100)='desc'--排序 只能取desc或者asc
as
--by quxh 2005.7.19
declare
@sqlnvarchar(2000)

set @WhereStr = replace(@WhereStr, ';', '')
set @WhereStr = replace(UPPER(@WhereStr), 'DELETE', '')
set @WhereStr = replace(@WhereStr, 'DROP', '')
set @WhereStr = replace(@WhereStr, 'UPDATE', '')
set @WhereStr = replace(@WhereStr, 'FROM', '')
set @WhereStr = replace(@WhereStr, '--', '')
set @WhereStr = replace(@WhereStr, 'EXECUTE', '')

if @WhereStr = '' begin
set @WhereStr = '1=1'
end

if @PageCurrent = 0 begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
end

else begin
if upper(@FdOrder) = 'DESC' begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc'
end
else begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc'
end
end
--print @sql
execute(@sql)

if(@RecordCount is null or @RecordCount<0)begin
declare @tsql nvarchar(200)
set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr
exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output
select @RecordCount
end
GO