一个根据SortOrder控制排序的存储过程

表结构如：

TacticID	uniqueidentifier
ParentTacticID	uniqueidentifier
EnterpriseUID	uniqueidentifier
Name	nvarchar(256)
SortOrder	int

其中有父子关系和以SortOrder进行排序，以下的存储过程用于控制同级下的排序更新。

CREATE   PROCEDURE  dbo.emb_Performance_Tactic_UpdateSortOrder
(
     @TacticID   uniqueidentifier ,
     @MoveUp   bit
)
AS  
SET   Transaction   Isolation   Level   Read   UNCOMMITTED   -- 设置事务
BEGIN
     SET  NOCOUNT  ON
     -- 定义变量
     DECLARE   @currentSortValue   int                  -- 当前SortOrder值
     DECLARE   @replaceSortValue   int                  -- 要替换的SortOrder值
     DECLARE   @replaceTacticID   uniqueidentifier      -- 要替换的TacticID值
     DECLARE   @ParentTacticID       uniqueidentifier      -- 当前父TacticID值

     -- 获取当前的SortOrder值
     SELECT   @currentSortValue   =  SortOrder,  @ParentTacticID   =  ParentTacticID  FROM  EMB_Performance_Tactic  WHERE  TacticID  =   @TacticID  
    
     -- 上移还是下移
     IF  ( @MoveUp   =   1 )
         BEGIN
         -- 上移操作
             -- 获取要替换的SortOrder
             SELECT   @replaceSortValue   =   COALESCE (t.SortOrder,  - 1 ),  @replaceTacticID   =   COALESCE (t.TacticID,  ' {00000000-0000-0000-0000-000000000000} ' )
                 FROM  EMB_Performance_Tactic t
                     inner   join  (
                         select   top   1   *  
                         from  EMB_Performance_Tactic 
                         WHERE  ParentTacticID  =   @ParentTacticID   and  SortOrder  <   @currentSortValue   order   by  SortOrder  DESC
                    )  as  pf  on  
                        pf.TacticID  =  t.TacticID

             if (  @replaceSortValue   !=   - 1  )
             begin     
                 UPDATE  EMB_Performance_Tactic  SET  SortOrder  =   @currentSortValue   WHERE  TacticID  =   @replaceTacticID   -- 更新要替换项的SortOrder为当前值
                 UPDATE  EMB_Performance_Tactic  SET  SortOrder  =   @replaceSortValue   WHERE  TacticID  =   @TacticID   -- 更新当前项的SortOrder为要替换项的SortOrder值
             END  
         END
     ELSE
         BEGIN
         -- 下移操作
             SELECT   @replaceSortValue   =   COALESCE (t.SortOrder,  - 1 ),  @replaceTacticID   =   COALESCE (t.TacticID,  ' 00000000-0000-0000-0000-000000000000 ' )
                 FROM  EMB_Performance_Tactic t
                     inner   join  (
                         select   top   1   *  
                         FROM  EMB_Performance_Tactic 
                         WHERE  ParentTacticID  =   @ParentTacticID   and  SortOrder  >   @currentSortValue   order   by  SortOrder  ASC                 
                    )  as  pf  on  
                        pf.TacticID  =  t.TacticID


             if (  @replaceSortValue   !=   - 1  )
             BEGIN         
                 UPDATE  EMB_Performance_Tactic  SET  SortOrder  =   @currentSortValue   WHERE  TacticID  =   @replaceTacticID
                 UPDATE  EMB_Performance_Tactic  SET  SortOrder  =   @replaceSortValue   WHERE  TacticID  =   @TacticID
             END
         END
    
END

/**/ /*  COALESCE用法。
1、返回多个表达式中非空值的数据，如果所有表达式均为Null，则返回Null
2、多表达式必须是相同类型，或者可以隐性转换为相同类型。
*/
GO

http://www.ruiya.com


附加另一个例子:
1.数据表设计如下:

SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
CREATE   TABLE   [ dbo ] . [ RedirectService_Category ] (
     [ CategoryID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL ,
     [ ParentID ]   [ int ]   NOT   NULL ,
     [ Name ]   [ nvarchar ] ( 256 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL ,
     [ Description ]   [ nvarchar ] ( 2000 ) COLLATE Chinese_PRC_CI_AS  NULL ,
     [ IsEnabled ]   [ bit ]   NOT   NULL   CONSTRAINT   [ DF_RedirectService_Category_IsEnabled ]    DEFAULT  (( 1 )),
     [ SortOrder ]   [ int ]   NOT   NULL   CONSTRAINT   [ DF_RedirectService_Category_SortOrder ]    DEFAULT  (( 1 )),
     [ CreateDate ]   [ datetime ]   NOT   NULL   CONSTRAINT   [ DF_RedirectService_Category_CreateDate ]    DEFAULT  ( getdate ()),
     [ LastUpdatedDate ]   [ datetime ]   NOT   NULL   CONSTRAINT   [ DF_RedirectService_Category_LastUpdatedDate ]    DEFAULT  ( getdate ()),
  CONSTRAINT   [ PK_RedirectService_Category ]   PRIMARY   KEY   CLUSTERED  
(
     [ CategoryID ]   ASC
) WITH  (PAD_INDEX   =   OFF , IGNORE_DUP_KEY  =   OFF )  ON   [ PRIMARY ]
)  ON   [ PRIMARY ]

2.存储过程:

SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
--  =============================================
--  Author:        RedirectService_Category_UpdateSortOrder
--  Create date: 2006-01-06
--  Description:    更新分类的排序ID
--  =============================================
CREATE   PROCEDURE   [ dbo ] . [ RedirectService_Category_UpdateSortOrder ]
(
     @CategoryID   int ,     -- 对其操作的分类ID
     @MoveUp   bit              -- 是否向上移动
)
AS
BEGIN
    SET Transaction   Isolation   Level   Read   UNCOMMITTED  --设置事务
     BEGIN     
     SET  NOCOUNT  ON ;

     -- 定义变量
     DECLARE   @CurrentSortOrder   int          -- 当前SortOrder值
     DECLARE   @ParentID   int                  -- 当前父分类ID
     DECLARE   @ReplaceSortOrder   int          -- 要替换的SortOrder值
     DECLARE   @ReplaceCategoryID   int          -- 要替换的CategoryID

     -- 为变量赋值
     SELECT   @CurrentSortOrder   =   [ SortOrder ] ,  @ParentID   =   [ ParentID ]
         FROM   [ RedirectService_Category ]   WHERE   [ CategoryID ]   =   @CategoryID

     -- 上移还是下移
     IF  ( @MoveUp   =   1 )
     BEGIN
         -- 上移操作
         -- 获取要替换的SortOrder和要替换的分类ID '<' 'DESC'
         SELECT   @ReplaceSortOrder   =   COALESCE (c. [ SortOrder ] ,  - 1 ),
                 @ReplaceCategoryID   =   COALESCE (c. [ CategoryID ] ,  - 1 )
             FROM   [ RedirectService_Category ]  c  INNER   JOIN  (
                 SELECT   TOP   1   *   FROM   [ RedirectService_Category ]  
                     WHERE   [ ParentID ]   =   @ParentID   AND   [ SortOrder ]   <   @CurrentSortOrder
                     ORDER   BY   [ SortOrder ]   DESC
            )  AS  pc  ON  pc. [ CategoryID ]   =  c. [ CategoryID ]
        
         -- 替换操作
         IF ( @ReplaceSortOrder   !=   - 1   AND   @ReplaceCategoryID   !=   - 1 )
         BEGIN
             -- 更新要替换项的SortOrder为当前值
             UPDATE   [ RedirectService_Category ]   SET   [ SortOrder ]   =   @CurrentSortOrder  
                 WHERE   [ CategoryID ]   =   @ReplaceCategoryID
             -- 更新当前项的SortOrder为要替换项的SortOrder值
             UPDATE   [ RedirectService_Category ]   SET   [ SortOrder ]   =   @ReplaceSortOrder  
                 WHERE   [ CategoryID ]   =   @CategoryID
         END
     END
     ELSE
     BEGIN
         -- 下移操作
         -- 获取要替换的SortOrder和要替换的分类ID    '>' 'ASC'
         SELECT   @ReplaceSortOrder   =   COALESCE (c. [ SortOrder ] ,  - 1 ),
                 @ReplaceCategoryID   =   COALESCE (c. [ CategoryID ] ,  - 1 )
             FROM   [ RedirectService_Category ]  c  INNER   JOIN  (
                 SELECT   TOP   1   *   FROM   [ RedirectService_Category ]  
                     WHERE   [ ParentID ]   =   @ParentID   AND   [ SortOrder ]   >   @CurrentSortOrder
                     ORDER   BY   [ SortOrder ]   ASC
            )  AS  pc  ON  pc. [ CategoryID ]   =  c. [ CategoryID ]

         -- 替换操作
         IF ( @ReplaceSortOrder   !=   - 1   AND   @ReplaceCategoryID   !=   - 1 )
         BEGIN
             -- 更新要替换项的SortOrder为当前值
             UPDATE   [ RedirectService_Category ]   SET   [ SortOrder ]   =   @CurrentSortOrder  
                 WHERE   [ CategoryID ]   =   @ReplaceCategoryID
             -- 更新当前项的SortOrder为要替换项的SortOrder值
             UPDATE   [ RedirectService_Category ]   SET   [ SortOrder ]   =   @ReplaceSortOrder  
                 WHERE   [ CategoryID ]   =   @CategoryID
         END
     END

     END
END

2007-05-16 附加另处一个例子

USE   [ PermissionService ]
GO
/**/ /****** 对象:  Table [dbo].[ps_Targets]    脚本日期: 05/16/2007 14:04:44 ******/
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
CREATE   TABLE   [ dbo ] . [ ps_Targets ] (
     [ ApplicationId ]   [ uniqueidentifier ]   NOT   NULL ,
     [ TargetId ]   [ uniqueidentifier ]   NOT   NULL   CONSTRAINT   [ DF_ps_Targets_TargetId ]    DEFAULT  ( newid ()),
     [ ParentId ]   [ uniqueidentifier ]   NOT   NULL ,
     [ Name ]   [ nvarchar ] ( 256 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL ,
     [ Url ]   [ nvarchar ] ( 500 ) COLLATE Chinese_PRC_CI_AS  NULL ,
     [ Description ]   [ nvarchar ] ( 2000 ) COLLATE Chinese_PRC_CI_AS  NULL ,
     [ SortOrder ]   [ int ]   NOT   NULL ,
     [ CreateDate ]   [ datetime ]   NOT   NULL   CONSTRAINT   [ DF_Targets_CreateDate ]    DEFAULT  ( getdate ()),
     [ LastUpdatedDate ]   [ datetime ]   NOT   NULL   CONSTRAINT   [ DF_Targets_LastUpdatedDate ]    DEFAULT  ( getdate ()),
  CONSTRAINT   [ PK_ps_Targets ]   PRIMARY   KEY   NONCLUSTERED  
(
     [ TargetId ]   ASC
) WITH  (IGNORE_DUP_KEY  =   OFF )  ON   [ PRIMARY ]
)  ON   [ PRIMARY ]

USE   [ PermissionService ]
GO
/**/ /****** 对象:  StoredProcedure [dbo].[ps_Targets_UpdateSortOrder]    脚本日期: 05/16/2007 14:05:37 ******/
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO

--  =============================================
--  Author:        BillChen
--  Create date: 2007-05-15
--  Description:    ps_Targets_UpdateSortOrder
--  =============================================
CREATE   PROCEDURE   [ dbo ] . [ ps_Targets_UpdateSortOrder ]
(
     @TargetId   uniqueidentifier ,     -- 对其操作的目标Id
     @MoveUp   bit                      -- 是否向上移动
)
AS
SET   Transaction   Isolation   Level   Read   UNCOMMITTED   -- 设置事务
BEGIN
     SET  NOCOUNT  ON ;

     -- 定义变量
     DECLARE   @CurrentSortOrder   int                  -- 当前SortOrder值
     DECLARE   @ParentId   uniqueidentifier              -- 当前父分类ID
     DECLARE   @ReplaceSortOrder   int                  -- 要替换的SortOrder值
     DECLARE   @ReplaceTargetId   uniqueidentifier      -- 要替换的TargetId
     DECLARE   @ApplicationId      uniqueidentifier      -- 当前节点所属的ApplicationId
    
     -- 为变量赋值
     SELECT   @CurrentSortOrder   =   [ SortOrder ] ,  @ParentId   =   [ ParentId ] ,  @ApplicationId   =   [ ApplicationId ]  
         FROM   [ ps_Targets ]   WHERE   [ TargetId ]   =   @TargetId

     -- 上移还是下移
     IF  ( @MoveUp   =   1 )
     BEGIN
         -- 上移操作
         -- 获取要替换的SortOrder和要替换的TargetId '<' 'DESC'
         SELECT   @ReplaceSortOrder   =   COALESCE (t. [ SortOrder ] ,  - 1 ),
             @ReplaceTargetId   =   COALESCE (t. [ TargetId ] ,  ' 00000000-0000-0000-0000-000000000000 ' )
             FROM   [ ps_Targets ]  t  INNER   JOIN  (
                 SELECT   TOP   1   *   FROM   [ ps_Targets ]  
                     WHERE   [ ApplicationId ]   =   @ApplicationId   AND   [ ParentId ]   =   @ParentId  
                         AND   [ SortOrder ]   <   @CurrentSortOrder  
                     ORDER   BY   [ SortOrder ]   DESC
            )  AS  pt  ON  pt. [ TargetId ]   =  t. [ TargetId ]
        
         -- print @ReplaceSortOrder;
         -- print @ReplaceTargetId;
         -- print @TargetId;
         -- 替换操作
         IF ( @ReplaceSortOrder   !=   - 1   AND   @ReplaceTargetId   !=   @TargetId )
         BEGIN
             -- 更新要替换项的SortOrder为当前值
             UPDATE   [ ps_Targets ]   SET   [ SortOrder ]   =   @CurrentSortOrder  
                 WHERE   [ TargetId ]   =   @ReplaceTargetId
             -- 更新当前项的SortOrder为要替换项的SortOrder值
             UPDATE   [ ps_Targets ]   SET   [ SortOrder ]   =   @ReplaceSortOrder  
                 WHERE   [ TargetId ]   =   @TargetId
         END
     END
     ELSE
     BEGIN
         -- 下移操作
         -- 获取要替换的SortOrder和要替换的TargetId '>' 'ASC'
         SELECT   @ReplaceSortOrder   =   COALESCE (t. [ SortOrder ] ,  - 1 ),
             @ReplaceTargetId   =   COALESCE (t. [ TargetId ] ,  ' 00000000-0000-0000-0000-000000000000 ' )
             FROM   [ ps_Targets ]  t  INNER   JOIN  (
                 SELECT   TOP   1   *   FROM   [ ps_Targets ]  
                     WHERE   [ ApplicationId ]   =   @ApplicationId   AND   [ ParentId ]   =   @ParentId  
                         AND   [ SortOrder ]   >   @CurrentSortOrder
                     ORDER   BY   [ SortOrder ]   ASC
            )  AS  pt  ON  pt. [ TargetId ]   =  t. [ TargetId ]

         -- print @ReplaceSortOrder;
         -- print @ReplaceTargetId;
         -- print @TargetId;
         -- 替换操作
         IF ( @ReplaceSortOrder   !=   - 1   AND   @ReplaceTargetId   !=   @TargetId )
         BEGIN
             -- 更新要替换项的SortOrder为当前值
             UPDATE   [ ps_Targets ]   SET   [ SortOrder ]   =   @CurrentSortOrder  
                 WHERE   [ TargetId ]   =   @ReplaceTargetId  
             -- 更新当前项的SortOrder为要替换项的SortOrder值
             UPDATE   [ ps_Targets ]   SET   [ SortOrder ]   =   @ReplaceSortOrder  
                 WHERE   [ TargetId ]   =   @TargetId
         END

     END

     -- 更新修改时间
     UPDATE   [ ps_Targets ]   SET   [ LastUpdatedDate ]   =   getdate ()  WHERE   [ TargetId ]   =   @TargetId

END

转载于:https://www.cnblogs.com/chenjunbiao/archive/2005/08/31/1760273.html