IDC资讯中心
这是经我该写后的存储过程
———————————————————
alter procedure usp_getrecordfrompage
@tblname varchar(1000), — 表名
@selectfieldname varchar(4000), — 要显示的字段名(不要加select)
@strwhere varchar(4000), — 查询条件(注意: 不要加 where)
@orderfieldname varchar(255), — 排序索引字段名
@pagesize int , — 页大小
@pageindex int = 1, — 页码
@irowcount int output, — 返回记录总数
@ordertype bit = 0 — 设置排序类型, 非 0 值则降序
as
declare @strsql varchar(4000) — 主语句
declare @strtmp varchar(4000) — 临时变量
declare @strorder varchar(400) — 排序类型
declare @strrowcount nvarchar(4000) — 用于查询记录总数的语句
set @orderfieldname=ltrim(rtrim(@orderfieldname))
if @ordertype != 0
begin
set @strtmp = <(select min
set @strorder = order by + @orderfieldname + desc
end
else
begin
set @strtmp = >(select max
set @strorder = order by + @orderfieldname + asc
end
set @strsql = select top + str(@pagesize) + @selectfieldname+ from
+ @tblname + where + @orderfieldname + @strtmp + (
+ right(@orderfieldname,len(@orderfieldname)-charindex(.,@orderfieldname)) + ) from (select top + str((@pageindex-1)*@pagesize)
+ @orderfieldname + from + @tblname + @strorder + ) as tbltmp)
+ @strorder
if @strwhere !=
set @strsql = select top + str(@pagesize) + @selectfieldname+ from
+ @tblname + where + @orderfieldname + @strtmp + (
+ right(@orderfieldname,len(@orderfieldname)-charindex(.,@orderfieldname)) + ) from (select top + str((@pageindex-1)*@pagesize)
+ @orderfieldname + from + @tblname + where + @strwhere +
+ @strorder + ) as tbltmp) and + @strwhere + + @strorder
if @pageindex = 1
begin
set @strtmp =
if @strwhere !=
set @strtmp = where + @strwhere
set @strsql = select top + str(@pagesize) + @selectfieldname+ from
+ @tblname + @strtmp + + @strorder
end
exec(@strsql)
if @strwhere!=
begin
set @strrowcount = select @irowcount=count(*) from + @tblname+ where +@strwhere
end
else
begin
set @strrowcount = select @irowcount=count(*) from + @tblname
end
exec sp_executesql @strrowcount,n@irowcount int out,@irowcount out
