create proc sp_Move_tree_Nodes
@MoveTreeCode varchar(200),
@MaxTreeCode varchar(200)
as
--开启事务
Begin TransAction
declare @errno int =0 --错误记录
update Tree set SceneryCode=REPLACE(scenerycode,@MoveTreeCode,@MaxTreeCode)
from tree where SceneryCode like @MoveTreeCode+'%'
set @errno=@errno+@@error --加上执行过程中产生的错误编号
-- 根据是否产生错误决定事务是提交还是撤销
If @errno>0
begin
print('事务处理失败,回滚事务!')
rollback TransAction
end
Else
Begin
print('事务处理成功,提交事务!')
Commit TransAction
End
'002003001013'--- 查询最大编号+1 如找到 00200300102+1 =002003001013
(select top 1 scenerycode tree order by scenerycode desc) +1 =002003001013
--移动就是更新 code 状态
update Tree set SceneryCode=REPLACE(scenerycode,'002003002','002003001013')
from tree where and SceneryCode like '002003002%'
--查询
select SceneryCode=REPLACE(scenerycode,'002003002','002003001013') from Tree
where SceneryCode like '002003002%'
--查询 当前节点下一级节点
select * from Tree where SceneryCode like '005005002___'
--查询 当前节点 子子节点
select * from Tree where SceneryCode like '005005002%'
总结: 移动就是 头部换了,身体不用换