sql处理排序

create PROCEDURE [dbo].[Sort]
(
    @ModID BIGINT,
    @ModeIDList VARCHAR(MAX),    
    @Result INT = 0 OUTPUT  -- 1:成功 -1:失败
)
AS
BEGIN
BEGIN TRAN    
    SAVE TRAN Sort_TRAN
    
    DECLARE @ID VARCHAR(30)
    DECLARE @Index INT
    DECLARE @OrderID INT SET @OrderID = 1
    
    SET @Index = charindex(',',@ModeIDList)
    WHILE(@Index > 0)
        BEGIN
            SET @ID = substring(@ModeIDList,1,@index-1)
            SET @ModeIDList = substring(@ModeIDList,@Index+1,len(@ModeIDList))
            SET @Index = charindex(',',@ModeIDList)

            Update dbo.table SET OrderID = @OrderID Where ModID = @ModID and ID =  @ID
                
            SET @OrderID = @OrderID + 1
            
            IF @@ERROR <> 0
                GOTO Error
        END
    END
    
    set @Result = 1

    COMMIT TRAN
    RETURN

Error:
    /*如果发生错误,返回-1*/
    ROLLBACK TRAN Sort_TRAN
    COMMIT TRAN
    SET @Result=-1
    Print @Result
    RETURN
View Code

exec Sort 1,'3,2,5,4,',1--字符串list

原文地址:https://www.cnblogs.com/lccnblog/p/3410475.html