一个根据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