@tblNamevarchar(255), -- 表名 @fldNamevarchar(255), -- 字段名 @OrderTypebit=0, -- 设置排序类型, 非 0 值则降序 @IsCountbit=0, -- 设置排序类型, 非 0 值则降序 @strWherevarchar(3000) ='', -- 查询条件 (注意: 不要加 where) @StartIndexvarchar(255), @maxRowsvarchar(255) AS declare@strSQLvarchar(3000) -- 主语句 declare@strCountvarchar(3000) -- 返回数据的总条数语句 declare@strOrdervarchar(3000) -- 排序类型语句 if@OrderType!=0 begin set@strOrder=' order by '+@fldName+' desc' end else begin set@strOrder=' order by '+@fldName+' asc' end set@strSQL='select * from (select *, ROW_NUMBER() OVER ('+@strOrder+') as Pos from '+@tblName+') as T where T.Pos > ('+@StartIndex+') and T.Pos <'+@maxRows set@strCount='select count(*) as Total from '+@tblName+' as count' if@strWhere!='' begin set@strSQL='select * from (select *, ROW_NUMBER() OVER ('+@strOrder+') as Pos from '+@tblName+' where '+@strWhere+') as T where T.Pos > ('+@StartIndex+') and T.Pos <'+@maxRows set@strCount='select count(*) as Total from '+@tblName+' as count where '+@strWhere end if@IsCount!=0----按需要返回数据的总条数 begin exec (@strCount) end exec (@strSQL) IFEXISTS (SELECT name FROM sysobjects WHERE name ='PageShowOne'AND type ='P') DROPPROCEDURE PageShowOne GO CREATEProc PageShowOne @PageSizeint=10 ,--每页显示的记录数 @PageCurrentint=1 ,--当前要显示的页号 @FdNamevarchar(100)='' ,--主键名或者标识列名 @SelectStrvarchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。 @FromStrvarchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable @WhereStrvarchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等 @OrderByStrvarchar(1000)='',--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等 @CountRowsint=0 output, --返回记录总数 @CountPageint=0 output --返回总页数 as --------定义局部变量--------- declare@Id1varchar(20),@Id2varchar(20) --开始和结束的记录号 declare@OrderBySqlsvarchar(1000) --order by 子句 declare@WhereSqlsvarchar(2000) --where 子句 declare@Sqlsnvarchar(4000) --最终组合成的Sqls语句 declare@TmpStrvarchar(2000) --临时 ---------------------------- if@OrderByStr<>'' set@OrderBySqls=' order by '+@OrderByStr else set@OrderBySqls='' -------- if@WhereStr<>'' set@WhereSqls=' where ('+@WhereStr+')' else set@WhereSqls='' -------- set@TmpStr=@WhereSqls --如果显示第一页,可以直接用top来完成 if@PageCurrent<=1 begin select@Id1=cast(@PageSizeasvarchar(20)) exec('select top '+@Id1+''+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls) goto LabelRes end --------------------------- select@Id1=cast(@PageSizeasvarchar(20)) ,@Id2=cast((@PageCurrent-1)*@PageSizeasvarchar(20)) ---------- if@WhereSqls<>'' set@WhereSqls=@WhereSqls+' and ('+@FdName+' not in(select top '+@Id2+''+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))' else set@WhereSqls=' where '+@FdName+' not in(select top '+@Id2+''+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')' ---------- set@Sqls='select top '+@Id1+''+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls exec (@Sqls) ----------- LabelRes: -----返回总记录数 set@Sqls='select @a=count(1) from '+@FromStr+@TmpStr exec sp_executesql @sqls,N'@a int output',@CountRows output -----返回总页数 if@CountRows<=@PageSize set@CountPage=1 else begin set@CountPage=@CountRows/@PageSize if (@CountRows%@PageSize) >0 set@CountPage=@CountPage+1 end return GO //----分页存储过程结束