方法一：
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
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法三：
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
------------------------------------------------------------------
按照saucer(思归)提供的网址
I decided to use the RowCount method wherever possible.
CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS
/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*Set sorting variables.*/
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)
/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
/*Set filter & group variables.*/
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 = ''
/*Execute dynamic query*/
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
浙公网安备 33010602011771号