mssqlserver分页 解决方案 之 分页存储过程

mssqlserver 通用分页存储过程

<wbr></wbr>要求表最好有唯一列,如自增,或主键等

<wbr></wbr> sqlsever2K支持'%'运算

<wbr></wbr> 总页数,总条数,分页数据,一步到位!

by sukun 08/06/26

<wbr></wbr>1.表名

<wbr></wbr>2.排序的列名

<wbr></wbr>3.排序条件

<wbr></wbr>4.当前页

<wbr></wbr>5.每页的条数

<wbr></wbr>6.自增的列名[以此列匹配数据]

<wbr></wbr>7.总条数[output]

<wbr></wbr>8.总页数[output]

 

create procedure up_common_select

@t_name<wbr></wbr> varchar(20),

@sortCol varchar(20),

@sortCon varchar(50),

@curPage int,

@piece<wbr></wbr><wbr></wbr> int,

@idsCol varchar(20),

@count<wbr></wbr> int output,

@pageCount int output

as

<wbr></wbr> declare @sql nvarchar(500);

--get count

set @sql='select @c = count(*) from '+@t_name;

<wbr></wbr> execute sp_executesql @sql,

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> <wbr></wbr><wbr></wbr> N'@c int output',

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> <wbr></wbr><wbr></wbr> @count

-- get pageCount

<wbr></wbr> if @count%@piece = 0

<wbr></wbr> begin

<wbr></wbr><wbr></wbr><wbr></wbr> set @pageCount=@count/@piece

<wbr></wbr> end

<wbr></wbr> else

<wbr></wbr> begin

<wbr></wbr><wbr></wbr><wbr></wbr> set @pageCount=@count/@piece+1

<wbr></wbr> end;<wbr></wbr><wbr></wbr><wbr></wbr>

-- page

set @sql='select top '+cast(@piece as varchar(50))+' * from '+@t_name+' where '+@idsCol+' not in (

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> select top '+cast((@curPage-1)*@piece as varchar(50))+' '+@idsCol+' from '+@t_name+' order by '+@sortCol+' '+@sortCon+')';

<wbr></wbr> execute sp_executesql @sql;

go

<wbr></wbr>

--test

declare @count int,@pageCount int

execute up_common_select 'a','id','desc',2,5,'id',@count output,@pageCount output

print @count

print @pageCount

-- drop procedure up_common_select