存储过程分页.

CreatePROCEDURE [dbo].[Table_A_GetList]
@PageSize int, --页数
@PageIndex int,--页码
@Condition nvarchar(200),--where条件
@OrderBy nvarchar(200) --排序条件
 AS
SET NOCOUNT ON;
if(@Condition='' or @Condition is null)
SET @Condition='1 = 1 '

SET @Condition = replace(lower(@Condition),' where ',' ')

if(@OrderBy='' or @OrderBy is null)
set @OrderBy=' ID   DESC'
set @OrderBy = replace(lower(@OrderBy),' order ', ' ' )
set @OrderBy = replace(lower(@OrderBy),' by ',' ')

declare @sql nvarchar(2000)
set @sql =  '
select top'+ str(@PageSize)+ ' * from Table_A  A '

set @sql=@sql+' where A.ID<=
(SELECT MIN(ID) FROM
(SELECT TOP '+STR(@PageSize * (@PageIndex-1)+1)+' ID FROM Table_A WHERE '+ @Condition+'  order by ID desc) AS T)
and '+ @Condition +' Order By '+ @OrderBy

exec(@sql)

 

只能支持主键ID 排序 暂不支持其他条件排序