分页存储过程

 

CREATE PROCEDURE sp_GetPagedData
(
@sTable nvarchar(1000),  --表名
@sPkey nvarchar(50),            --主键(一定要有)
@sField nvarchar(1000)='*',     --字段
@iPageCurr int,                 --当前页数
@iPageSize int,                 --每页记录数
@sCondition nvarchar(1000),  --条件(不需要where)
@sOrder nvarchar(200) ,         --排序(不需要order by,需要asc和desc字符)
@Counts int=0 output,           --记录条数(已有值:外部赋值,0执行count)
@pageCount int=1 output     --查询结果分页后的总页数
)
AS
SET NOCOUNT ON

DECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000)
DECLARE @iAsc int,@iDesc int,@iType tinyint
DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000)

/*----------------------判断where 条件是否空值-------------------*/
IF LEN(@sCondition)>2
 SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND '
 ELSE
 BEGIN
  SELECT @sC1='', @sC2=' WHERE '
 END

SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder='', @sT4=UPPER(@sPkey)

/*-----------------------获取查询的数据行数---------------------*/
IF LEN(@sT2)>2
BEGIN
 SELECT @iAsc=0, @iDesc=0
 IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建
     BEGIN
  SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1)
 END

 IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc))
  SELECT @iType=1, @sT3='>(SELECT MAX('
 ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))
 BEGIN
  SELECT @iType=1, @sT3='<(SELECT MIN('
 END
 SET @sOrder=' ORDER BY '+@sT2
END

/*-------------------------获取查询的数据行数----------------------*/

--IF (@Counts<1)
--BEGIN
 --SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1
 --EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT
--END

SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1
EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT

SET @pageCount=(@Counts+@ipageSize-1)/@ipageSize

IF @iPageCurr>@pageCount
 SET @iPageCurr=@pageCount

SELECT @iPageCurr=(CASE WHEN @Counts<(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr<1 THEN 1 ELSE @iPageCurr END)

IF (@iPageCurr>1) AND (@iType=1)
 SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder
ELSE IF (@iPageCurr>1) AND (@iType=0)
 SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder
ELSE
BEGIN
    SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder
END

EXEC(@sSQL)

GO