原存储过程如下。这里的排序字段,只能设一个,但我需要两个,所以我把set @strOrder = 'order by ' + @fldName + ' desc',这句改为set @strOrder = 'order by IsTop desc, ' + @fldName + ' desc'后,不能正常分页。这是论坛浏览帖子列表的分页,必须要两个字段排序,望高手赐教,改如何改。CREATE PROCEDURE P_Club_TopicList
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@doCount bit =0, -- 返回记录总数, 非 0 值则返回
@OrderType bit =1, -- 设置排序类型, 非 0 值则降序
@boardID int
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strWhere varchar(1500) -- 查询条件 (注意: 不要加 where)if @boardID = 0
set @strWhere = 'IsTop<>3'
else
set @strWhere = 'BoardID=' + str(@boardID, len(@boardID)) + ' or BoardID in (select BoardID from Club_Board where ParentID=' + str(@boardID, len(@boardID)) + ') and IsTop<>3'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'
--如果@OrderType不是0,就执行降序,这句很重要!
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,len(@PageSize)) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere +' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL ='select top ' + str(@PageSize,len(@PageSize)) +' ' +@strGetFields+' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top '+str((@PageIndex-1)*@PageSize) + '['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ')as tblTmp)' + @strOrder
if @strWhere != ''
begin
set @strSQL ='select top ' + str(@PageSize,len(@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
end
exec (@strSQL)
GO
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@doCount bit =0, -- 返回记录总数, 非 0 值则返回
@OrderType bit =1, -- 设置排序类型, 非 0 值则降序
@boardID int
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strWhere varchar(1500) -- 查询条件 (注意: 不要加 where)if @boardID = 0
set @strWhere = 'IsTop<>3'
else
set @strWhere = 'BoardID=' + str(@boardID, len(@boardID)) + ' or BoardID in (select BoardID from Club_Board where ParentID=' + str(@boardID, len(@boardID)) + ') and IsTop<>3'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'
--如果@OrderType不是0,就执行降序,这句很重要!
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,len(@PageSize)) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere +' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL ='select top ' + str(@PageSize,len(@PageSize)) +' ' +@strGetFields+' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top '+str((@PageIndex-1)*@PageSize) + '['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ')as tblTmp)' + @strOrder
if @strWhere != ''
begin
set @strSQL ='select top ' + str(@PageSize,len(@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
end
exec (@strSQL)
GO
解决方案 »
- 如何通过一个按钮停止、启动定时器
- 关于缓存分页问题,,,
- 在ASP.NET中怎么获取用户登陆的次数(使用COOKIE)?
- 在 win xp 中用asp.net发邮件 还需要在IIS中进行什么设置么?
- 为什么cookie 在服务器不响应?
- 面试官的提问
- 郁闷死了,关于datagrid的分页问题,怎么页码指示没有链接?
- 数据库访问问题,两个~(100分)
- URL重写地址和网站的真实目录冲突了,怎么办?
- 我安装了.NET v1.0,总是连续地弹出一个错误对话框,显示“应用程序发生异常 未知的软件异常”
- 请问微软vs.net2005,asp.net2.0在哪里可以下载?
- datareader提示没有打开的连接错误
@PAGESIZE INT, --每页的记录数
@PAGEINDEX INT --当前页
AS
SET NOCOUNT ON
BEGIN
DECLARE @INDEXTABLE TABLE(ID INT IDENTITY(1,1),NID INT) --定义表变量DECLARE @PAGELOWERBOUND INT --定义此页的底码DECLARE @PAGEUPPERBOUND INT --定义此页的顶码SET @PAGELOWERBOUND=(@PAGEINDEX-1)*@PAGESIZESET @PAGEUPPERBOUND=@PAGELOWERBOUND+@PAGESIZESET ROWCOUNT @PAGEUPPERBOUNDINSERT INTO @INDEXTABLE(NID) SELECT ID FROM TABLE1 ORDER BY ID DESCSELECT A.* FROM TABLE1 A
INNER JOIN @INDEXTABLE T
ON A.ID=T.NID
WHERE T.ID>@PAGELOWERBOUND AND T.ID<=@PAGEUPPERBOUND ORDER BY T.ID
END
SET NOCOUNT OFF