＋设为首页 ＋添加到收藏夹
在这个论坛里找到了下面这个分页存储过程。
CREATE PROCEDURE PageGo
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = *, -- 需要返回的列
@fldName varchar(255)=, -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=
set @strSQL = select count(*) as Total from [ + @tblName + ] where +@strWhere
else
set @strSQL = select count(*) as Total from [ + @tblName + ]
end
else
begin
if @OrderType != 0
begin
set @strTmp = <(select min
set @strOrder = order by [ + @fldName +] desc
end
else
begin
set @strTmp = >(select max
set @strOrder = order by [ + @fldName +] asc
end
if @PageIndex = 1
begin
if @strWhere !=
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from [ + @tblName + ] where + @strWhere + + @strOrder
else
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from [+ @tblName + ] + @strOrder
end
else
begin
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from [
+ @tblName + ] where [ + @fldName + ] + @strTmp + ([+ @fldName + ]) from (select top + str((@PageIndex-1)*@PageSize) + [+ @fldName + ] from [ + @tblName + ] + @strOrder + ) as tblTmp)+ @strOrder
if @strWhere !=
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from [
+ @tblName + ] where [ + @fldName + ] + @strTmp + ([
+ @fldName + ]) from (select top + str((@PageIndex-1)*@PageSize) + [
+ @fldName + ] from [ + @tblName + ] where + @strWhere +
+ @strOrder + ) as tblTmp) and + @strWhere + + @strOrder
end
end
exec (@strSQL)
GO
------------
我在查询分析器里调用：
PageGo joblist,jid,subcatelog,jobname,salary,publishtime,publishtime,10,1,1,1,username=admin
得到的结果是：Total
2
这条语句选出的结果一共是2条没错，我在前台要怎么调用分页存储过程呢？怎么写到记录集里？不明白！
@mytype is null
select * from [table]
where company like +%+isnull(@company,company)+%
and
mytype = isnull(@mytype,mytype)
帮你顶下
