分页查询与存储过程

1、分页查询第10条-第20条记录（四种方法）

<!--第一种：Top-->

select top(10)* from AdminInfo where Id not in(select top(10) Id from AdminInfo)

<!--第二种：Max-->

select top(10) * from  AdminInfo where Id>(select MAX(Id)from AdminInfo 
where Id in (select top(10)Id from AdminInfo))

<!--第三种：Between ...and ...-->

select * from AdminInfo where Id between 1 and 20

<!--第四种:ROW_NUMBER()-->

select *from(select *,ROW_NUMBER()over(order by id)as number from AdminInfo)t where t.number between 11 and 20

2、存储过程（两种方法）

  create proc 存储名称(@参数名 参数类型)as

  declare @变量名 参数类型；

  set @变量名=值;

  法一：

create proc pagelistproc (@pageIndex int,
@pagesize int,@tableName varchar(200),@columnName varchar(500),@orderby varchar(50),@sort varchar(50)
)as
declare @sql nvarchar(2000);
set @sql='select  '+@columnName+'  from(select '+@columnName+' ,ROW_NUMBER()over(order by '+@orderby+' )as number from '+@tableName+' )t
where t.number between '+cast(((@pageIndex-1)*@pagesize)as varchar(200))+' and '+ cast((@pagesize*@pageIndex) as varchar(200))
exec(@sql)
--<!--传值-->
exec pagelistproc 1,10,'dbo.Admini','*','aID','desc';

法二：

create proc Prceduer(@count int)as
 declare @countq int;
 set @countq=@count;
 while(@countq>0)
 begin
 print @countq;
 set @countq=@countq-1;
 End