SQL与oracle分页存储过程

SQL分页存储过程

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE PROCEDURE [dbo].[sp_PageIndex]
(
 — Add the parameters for the stored procedure here
 @tblName varchar(50),         –(表名、视图名)
 @strGetFields varchar(255)=’*’, –(查询字段列表)
 @orderName varchar(255),         –(排序字段名称)
 @maxRows int ,                 –(每页显示记录数)
 @StartIndex int,                 –(当前页码)
 @IsCount bit=0,                 –是否返回查询总记录数 (0: no;1:yes)
 @OrderType bit=0,               –排序类型名称(0:升序;1:降序)
 @strWhere varchar(3000)=”      –查询条件（where部分，不用加where关键字）
)
AS
BEGIN
 — SET NOCOUNT ON added to prevent extra result sets from
 — interfering with SELECT statements.
 SET NOCOUNT ON;

    — Insert statements for procedure here
    declare @strSql varchar(5000)
    declare @strTemp varchar(100)
    declare @strOrder varchar(100)
    declare @strCount varchar(3000) — 返回数据的总条数语句
  if @orderType!=0
   begin
     set @strTemp='<(select min’
     set @strOrder=’order by [‘+@orderName+’] desc’
   end
  else
   begin
     set @strTemp=’>(select max’
     set @strOrder=’order by [‘+@orderName+’] asc’
   end

  if @StartIndex=1
   begin
   if @strWhere!=”
    begin
        set @strSql=’select top ‘+str(@maxRows)+’ ‘+@strGetFields+’ from [‘+@tblName+’]where ‘+@strWhere+’ ‘+@strOrder
        SET @strCount = ‘select count(*) as Total from ‘ + @tblName + ‘ as count where ‘ + @strWhere
    end
   else
    begin
       set @strSql=’select top ‘+str(@maxRows)+’ ‘+@strGetFields+’ from [‘+@tblName+’] ‘+@strOrder
       SET @strCount = ‘select count(*) as Total from ‘ + @tblName
    end
   end

  else
   begin
   if @strWhere!=”
    begin
        set @strSql=’select top ‘+str(@maxRows)+’ ‘+@strGetFields+’ from [‘+@tblName+’] where [‘
         +@orderName+’] ‘+@strTemp+'([‘+@orderName+’]) from (select top ‘+str((@StartIndex-1)*@maxRows)
         +’ [‘+@orderName+’] from [‘+@tblName+’] where ‘+@strWhere+’ ‘+@strOrder+’) as tableTemp) and ‘
         +@strWhere+’ ‘+@strOrder
        SET @strCount = ‘select count(*) as Total from ‘ + @tblName + ‘ as count where ‘ + @strWhere
    end
   else
    begin
        set @strSql=’select top ‘+str(@maxRows)+’ ‘+@strGetFields+’ from [‘+@tblName+’] where [‘+@orderName+’] ‘+@strTemp+'([‘+@orderName+’]) from (select top ‘+str((@StartIndex-1)*@maxRows)
        +’ [‘+@orderName+’] from [‘+@tblName+’]’+@strOrder+’) as tableTemp) ‘+@strOrder
        SET @strCount = ‘select count(*) as Total from ‘ + @tblName
    end
   end

if @IsCount!=0 —-按需要返回数据的总条数
begin
exec (@strCount)
end

exec (@strSql)
END
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////