存储过程

1：最原始的写法

select top 4 * from Classes order by CID

select top 4 * from Classes where CID not in (select top 4 CID from Classes order by CID asc) order by CID asc

 

2:使用ROW_NUMBER
select * from (select ROW_NUMBER() over(order by CID) as RNum, * from Classes) as tmp where RNum >4 and RNum<=8;
注意在选择RNum的过程中，由于RNum是刚产生的，如果不加前面的select * from 会出现错误。
 
3:创建存储过程
create proc up_GetPagedData
@pi int,  --页码
@ps int  --页容量
as
begin
 select * from (select ROW_NUMBER() over(order by CID) as RNum,* from Classes)
 as tmp where RNum>(@pi-1)*@ps and RNum<=@pi*@ps
end

exec up_GetPagedData 3,4

4：高效的存储过程

alter proc up_GetPagedData
@pi int,--页码
@ps int, --页容量
@rc float output, --总行数
@pc float output--总页数
as
begin
 select @rc=COUNT(cid) from Classes where CIsDel=0--查出总行数
 select @pc=CEILING(@rc/@ps) -- 算出总页数 CEILING(1.1)=2  floor(1.1)=1
 select * from (
 select ROW_NUMBER() over(order by cid) as RNum,* from Classes where CIsDel=0
 ) as temp
 where RNum >(@pi-1)*@ps and RNum<=@pi*@ps
end

declare @rc int,@pc int
exec up_GetPagedData 1,4,@rc output,@pc output
select @rc,@pc

--高效存储过程
USE [AMS]
GO
/****** Object:  StoredProcedure [dbo].[GetPageDataOutRowPageCount]    Script Date: 07/29/2011 15:47:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPageDataOutRowPageCount]
(
@tn nvarchar(30),--表名称
@idn nvarchar(20),--表主键名称
@pi int = 1,--当前页数
@ps  int = 7,--每页大小
@wh nvarchar(255) = '',--wehre查询条件
@oby nvarchar(255) = '',--orderby 排序
@rc int output,--总行数(传出参数)
@pc int output--总页数(传出参数)
)
AS
DECLARE @sql NVARCHAR(225),@sqlCount NVARCHAR(225)
IF @pi = 1
BEGIN
 SET @sqlCount = 'SELECT @rc=COUNT(['+@idn+']),@PageCount=CEILING((COUNT(+ CAST(@ps AS VARCHAR)+') FROM ' + @tn
  IF LEN(@wh)>1
   set @sqlCount=@sqlCount+'  WHERE '+@wh
--  IF LEN(@oby)>1
--   SET @sqlCount=@sqlCount +@oby
        EXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@PageCount INT OUTPUT',@rc OUTPUT,@pc OUTPUT
 SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn
 IF LEN(@wh)>1
  set @sql=@sql+' WHERE '+@wh
 IF LEN(@oby)>1
   SET @sql=@sql +@oby
print '+@wh
--  IF LEN(@oby)>1
--   SET @sqlCount=@sqlCount +@oby
        EXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@PageCount INT OUTPUT',@rc OUTPUT,@pc OUTPUT
  SET @PageLowerBound = @ps * (@pi-1)
  IF @PageLowerBound<1
   SET @PageLowerBound=1
  SET ROWCOUNT @PageLowerBound
  SET @sql=N'SELECT  @StartID = ['+@idn+'] FROM '+@tn
  IF len(@wh)>1
     set @sql=@sql+' WHERE '+@wh
        IF LEN(@oby)>1
   SET @sql=@sql +@oby 
print   SET ROWCOUNT 0
  SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn+' WHERE ['+@idn+']>'+ str(@StartID)
  IF LEN(@wh)>1
   set @sql=@sql+' AND '+@wh
  IF LEN(@oby)>1
   SET @sql=@sql +@oby
print --select @rc,@pc
----select count('org_id') as '行数',ceiling((count('org_id')+0.0)/15) as '页数' from ams_organ where org_isdel=0
----select 241/15