Sql两种存储过程（单排序，多排序）

单条件单排序

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

转载于:https://www.cnblogs.com/lilei107/archive/2011/07/26/2117046.html