分页存储过程

sp_Pagination

 

Create PROCEDURE [dbo].[sp_Pagination]
@table nvarchar(max), -- 表名称
@field nvarchar(max)=N'*', -- 字段名称
@where nvarchar(max)=N' Where 1=1',-- 条件
@pk nvarchar(20)=N'ID', -- 关键字
@order nvarchar(max)=N'', -- 排序
@pSize int=40, -- 页大小
@pCurrent int = 1 -- 当前页
AS
declare @page bigint
declare @m_exec nvarchar(4000)
declare @tsql11 nvarchar(4000)
if(@where is null OR @where='')
set @where=N' Where 1=1';
if(@pCurrent <= 1)
begin
set @pCurrent=1
set @m_exec = 'Select TOP ' + CAST(@pSize as varchar(100)) + ' ' + @field + ' From ' + @table + ' ' + @where +' '+ @order
print @m_exec
EXECUTE(@m_exec)
end

else
begin
set @page = @pSize * (@pCurrent - 1)
-- print @page
-- 分页的原理是根据它来的->select top 10 * from table1 where name not in(select top 8 name from table1)
set @m_exec = 'Select TOP ' + CAST(@pSize as varchar(100)) + ' ' + @field + ' From ' + @table + ' ' + @where + ' AND ' + @pk + ' not in(Select TOP ' + CAST(@page as varchar(100)) + ' ' + @pk + ' From ' + @table + ' ' + @where + ' ' + @order + ') ' +' '+ @order
--print @m_exec
EXECUTE(@m_exec)
end

set @tsql11 = 'Select COUNT(' + @pk + ') as n,CEILING(CAST(COUNT(' + @pk + ') as numeric(10,4))/CAST(' + rtrim(ltrim(str(@pSize))) + ' as numeric(10,4))) as p,' + rtrim(ltrim(str(@pSize))) + ' as size,' + rtrim(ltrim(str(@pCurrent))) + ' as pCur From ' + @table + ' ' + @where
EXECUTE(@tsql11)

--print @tsql11

 

转载于:https://www.cnblogs.com/wei2yi/archive/2012/02/01/2334826.html