幾個分頁預存程序

方法一：

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