SQL存储过程

CREATE procedure dbo.pro_updatetrade

@t_name nvarchar(20),
@t_parentid int,
@t_depth int,
@t_path nvarchar(20),
@t_count int,
@t_order int,
@t_pointcount int,
@t_id int

as

  begin
   declare @new_order int  --新的排序值
   declare @new_sql nvarchar(1000)  --拼凑的sql语句
   declare @old_order int --更新前的排序值
   
   --获取原来的排序值
   select  @old_order=t_order from bst_trade where t_id=@t_id 
   set @new_order=0

  if @t_order= @old_order  
     --不需要修改排序值
     update bst_trade set t_name=@t_name,t_parentid=@t_parentid,t_depth=@t_depth,t_path=@t_path,t_count=@t_count,t_pointcount=@t_pointcount where t_id=@t_id
   
  else
    --获取修改后的值，并更改排序值小于此值的所以记录
    begin
    select @new_order =max(t_Order) from  (select top @t_order  t_Order from bst_trade order by t_order asc ) as t 

  print @new_sql

     exec(@new_sql)
     print @new_order
    update bst_trade set t_order=t_order + 1 where t_order > @new_order -1 and t_parentid=@t_parentid
    update bst_trade set t_name=@t_name,t_parentid=@t_parentid,t_depth=@t_depth,t_path=@t_path,t_count=@t_count,t_order=@new_order,t_pointcount=@t_pointcount where t_id=@t_id
    end
 end
GO