USE [C6]
GO
/****** Object: StoredProcedure [dbo].[row_Number] Script Date: 01/02/2020 10:40:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[row_Number]
@tablName nvarchar(20), --表名
@pageIndex nvarchar, --当前页
@pageSize INT, --显示数量
@where nvarchar(2000), --条件
@ColName nvarchar(20), --排序列名
@orderBy nvarchar(10) --顺序
as
begin
declare @sql nvarchar(2000)
declare @sql1 nvarchar(2000)
set
@sql='with Task_Round as(
select (select CEILING(CAST(COUNT(id) as float)/'+CONVERT(nvarchar(20),@pageSize) +') as countPage from ' +@tablName+' where 1=1 '+@where+' ) as countPage,(select count(id) from '+@tablName+' where 1=1 '+@where+' ) as count, ROW_NUMBER() over(order by '+ @ColName+' '+ @orderBy+' ) as RowNumner,* from '+@tablName+' where 1=1 '+@where+'
)
select top '+convert(nvarchar(32),@pageSize) +' * from Task_Round where RowNumner >'+convert(nvarchar(32),(@pageIndex-1)*@pageSize);
execute(@sql);
end
细节还要在调整