一个支持多表,多字段排序,支持别名的分页,大家看看,随便问一个进程/锁的问题

lovelxj 2006-09-01 09:22:59
我在.net中使用单键模式,使用同一个数据库操作对象操作数据库,而且连接都有正常关闭,可是在SQL SERVER的企业管理器中发现进程/锁很多。 不知道这是为什么?

分页如下:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


create proc dbo.Up_SysGetObjectPageList
--分页存储过程,在多表排序时,排序的第一个字段的排序方向由 变量 @OrderType 来确定。
@TableName nvarchar(255) , -- 表名,可以以 " hmeThread t,hmePost p " 的格式,甚至可以包括子查询
@GetFields nvarchar(1000) = '*', -- 需要返回的列 ,返回的列要注意不能有歧义,否则可能出错
@OrderFieldName nvarchar(255)='', -- 排序的字段名 "p.post_ID desc ,p.createdDate asc "
@strWhere nvarchar(1500) = '', -- 查询条件 (注意: 不要加 where) ,格式为 " and a = b "
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType int = 0, -- 设置排序类型, 非 0 值则降序
@IsCount bit = 0 -- 设置是否统计,如果为真,则不返回数据集合,而是返回中选记录总数,反之则只返回数据集合
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp nvarchar(110) -- 临时变量
declare @strOrder nvarchar(400) -- 排序类型

declare @intPosTmp int --临时位置
declare @strFirstOrderFullName nvarchar(50) -- 排序字段的完整名称,有带别名,如t.thread_ID
declare @strFirstOrderName nvarchar(50) -- 排序字段的字段名称,不带别名,如 thread_ID
declare @strSubjoinOrder nvarchar(200) -- 附加的排序

set @intPosTmp = charindex(',',@OrderFieldName)
set @strSubjoinOrder =''

if @intPosTmp >0
begin
set @strFirstOrderFullName = SUBSTRING(@OrderFieldName,1,@intPosTmp-1)
set @strSubjoinOrder = SUBSTRING(@OrderFieldName,@intPosTmp,len(@OrderFieldName) - @intPosTmp+1)
end
else
set @strFirstOrderFullName = @OrderFieldName

set @strFirstOrderFullName = lower(@strFirstOrderFullName)
set @strFirstOrderFullName = replace(@strFirstOrderFullName,' asc','')
set @strFirstOrderFullName = replace(@strFirstOrderFullName,' desc','')
set @intPosTmp = charindex('.',@strFirstOrderFullName)
if @intPosTmp>0
set @strFirstOrderName = SUBSTRING(@strFirstOrderFullName,@intPosTmp+1,len(@strFirstOrderFullName)-@intPosTmp)
else
set @strFirstOrderName = @strFirstOrderFullName

begin

if @strWhere <> ''
set @strWhere = ' 1=1 '+@strWhere
else
set @strWhere = ' 1=1 '

if @OrderType != 0
begin
set @strTmp = '<(select min'
if @OrderFieldName<>''
set @strOrder = ' order by ' + @strFirstOrderFullName +' desc' + @strSubjoinOrder
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
if @OrderFieldName<>''
set @strOrder = ' order by ' + @strFirstOrderFullName + @strSubjoinOrder
end

if @PageIndex = 1
set @strSQL = 'select top ' + ltrim(str(@PageSize)) +' ' + @GetFields +' from ' + @TableName + ' where ' + @strWhere + @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度

else
begin


--以下代码赋予了@strSQL以真正执行的SQL代码
if @strWhere != ''
set @strSQL = 'select top ' + ltrim(str(@PageSize)) +' ' +@GetFields +' from '
+ @TableName + ' where '+ @strWhere + ' and ' + @strFirstOrderFullName + @strTmp + '('
+ @strFirstOrderName + ') from (select top ' + ltrim(str((@PageIndex-1)*@PageSize)) + ' '
+ @strFirstOrderFullName + ' from ' + @TableName + ' where ' + @strWhere
+ @strOrder + ') as tblTmp) ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +@GetFields +' from '
+ @TableName + ' where ' + @strFirstOrderFullName + @strTmp + '('+ @strFirstOrderName + ') from (select top ' + str((@PageIndex)*@PageSize) + @strFirstOrderFullName + ' from ' + @TableName + @strOrder + ') as tblTmp)'+ @strOrder
end
end
--select @strSQL
exec (@strSQL)

if @IsCount=1
exec('select count(*) from ' + @TableName + ' where ' + @strWhere )

return






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

...全文
351 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
yuezu1026 2007-04-23
  • 打赏
  • 举报
回复
并且这个分页,在order by 的第一个排序字段,不能用表达式
yuezu1026 2007-04-23
  • 打赏
  • 举报
回复
这个东西,我测试过的。
yuezu1026 2007-04-23
  • 打赏
  • 举报
回复
这个存储过程有问题,如果第一个排序列存在空值,那这个存储过程在第二页的时候根本就查不到数据。
CoolMoon1001 2006-10-26
  • 打赏
  • 举报
回复
有问题,如果有如下数据就不行了
ID F1
----------- -----------
12 1
10 1
8 1
6 1
4 1
2 1
11 0
9 0
7 0
5 0
3 0
1 0

按照 F1 desc,ID desc 排序,每页4个,第二页就不对了
lovelxj 2006-09-05
  • 打赏
  • 举报
回复
这个存储过程已经可以用了
可以自己调整,看看是要先返回总记录数,还是返回记录集合
mjldnz1 2006-09-01
  • 打赏
  • 举报
回复
mark 我正需要这样的存储过程,期待中!

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧