单条件单排序
ALTER PROCEDURE [dbo].[Universal_Infolist]
@tablename nvarchar(50),
@wherestr nvarchar(1000),
@oid nvarchar(20),
@oway nvarchar(50),
@pagesize int = 10,
@pageindex int=1,
@recordcount int =0 output
AS
declare @sqlstr nvarchar(1500)
if(@pageindex=1)
begin
set @sqlstr = ' select top '+Convert(nvarchar(20),@pagesize)+' * from '+@tablename+' where 1=1 '+@wherestr+' order by '+@oid+' '+@oway+' '
end
else
begin
set @sqlstr = ' select top '+Convert(nvarchar(20),@pagesize)+' * from '+@tablename+' where 1=1 '+@wherestr+' and '+@oid+' <(select min('+@oid+' ) from(select top '+convert(nvarchar(20), (@pageindex -1)*@pagesize)+ ' '+@oid+' from '+@tablename+' where 1=1 '+@wherestr+' order by '+@oid+' '+@oway+' ) as O ) order by '+@oid+' '+@oway+' '
end
Exec sp_executesql @sqlstr
--print @sqlstr;
set @sqlstr =' select count('+@oid+') from '+@tablename+' where 1=1 '+@wherestr+' '
exec sp_executesql @sqlstr
---------------------------------------------------
多条件多种排序
ALTER PROCEDURE [dbo].[Universal_Infolist_1]
@tablename nvarchar(50),
@wherestr nvarchar(1000),
@oid nvarchar(20),
@oway nvarchar(100),
@pagesize int = 10,
@pageindex int=1,
@recordcount int =0 output
AS
declare @sqlstr nvarchar(1500)
if(@pageindex=1)
begin
set @sqlstr = ' select top '+Convert(nvarchar(20),@pagesize)+' * from '+@tablename+' where 1=1 '+@wherestr+' order by '+@oway+' '
end
else
begin
set @sqlstr = ' select top '+Convert(nvarchar(20),@pagesize)+' * from '+@tablename+' where '+@oid+' not in (select top '+convert(nvarchar(20), (@pageindex -1)*@pagesize)+ ' '+@oid+' from '+@tablename+' where 1=1 '+@wherestr+' order by '+@oway+') '+@wherestr+' order by '+@oway+' '
end
Exec sp_executesql @sqlstr
--print @sqlstr;
set @sqlstr = ' select count('+@oid+') from '+@tablename+' where 1=1 '+@wherestr+' '
exec sp_executesql @sqlstr
