sql數據庫中常用的分頁 我做了一個萬能的 用的上的小伙伴拿去耍吧

go? ----SqlServer萬能分頁代碼

create procedure [dbo].[sp_datapager]

@pagesize int,--每一頁的大小

@pageindex int,--頁碼數

@tablename varchar(Max),--表的名稱

@keycolumn varchar(20),---主鍵id

@columns varchar(200),--要查詢出列的名稱

@where varchar(200),---查詢條件

@orderby varchar(100),---排序方式

@recordcount int out--輸出參數，非0則返回要查詢表的總記錄數

as

declare @sql nvarchar(3000)

declare @rcsql nvarchar(1000)

set @rcsql='select @rc=count(*) from '+@tablename

set @sql='select top '+convert(varchar(3),@pagesize)+' '+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+')'

if (@where!='')

begin

set @rcsql='select @rc=count(*) from '+@tablename+' where '+@where

set @sql='select top '+convert(varchar(3),@pagesize)+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+' where '+@where+') and '+@where

end

if (@orderby!='')

begin

if (@where!='')

begin

set @sql='select top '+convert(varchar(3),@pagesize)+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+' where '+@where+' order by '+@orderby+') and '+

@where+' order by ' +@orderby

end

else

begin

set @sql='select top '+convert(varchar(3),@pagesize)+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+' order by '+@orderby+')'+' order by ' +@orderby

end

end

declare @param nvarchar(100)

set @param='@rc int output'

exec sp_executesql @sql

exec sp_executesql @rcsql,@param,@rc=@recordcount output

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MySql通用分頁加條件

delimiter //

drop procedure if exists `WebPager`;

create DEFINER=`root`@`%` procedure WebPager

(

in tableName varchar(4000),-- 表關系

in selectField varchar(4000),-- 要查詢的字段

in strwhere varchar(4000),-- 條件

in ordering varchar(1000),-- 排序字段

in sort int,-- 排序方式 0表示順序，1表示倒序

in pageIndex int,-- 當前頁數

in pageSize int ,-- 每頁顯示的記錄數

out rows int -- 總行數

)

begin

-- declare sqlstr varchar(4000);

set @sqlstr=concat('select SQL_CALC_FOUND_ROWS ',selectField,' from ',tableName,' where 1=1 ');

if strwhere is not null then -- 判斷條件是否為空

set @sqlstr=concat(@sqlstr,' and ',strwhere);

end if;

if ordering is not null then

if sort=0 then

set @sqlstr=concat(@sqlstr,' order by ',ordering);

end if;

if sort=1 then

set @sqlstr=concat(@sqlstr,' order by ',ordering,' desc');

end if;

end if;

set @sqlstr=concat(@sqlstr,' limit ',(pageIndex-1)*pageSize,',',pageSize);

-- select sqlstr;

PREPARE distSQL FROM @sqlstr;

EXECUTE distSQL;

DEALLOCATE PREPARE distSQL;

set rows=FOUND_ROWS();-- 獲取總記錄數

end; //

-- call WebPager('card_manager','*','Del_Flag=0 and Balance>1000','Card_Code',1,1,2,@rows); select @rows;

總結

以上是生活随笔為你收集整理的mysql sqlserver分页_SqlServer、MySql万能分页代码的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。