Question

我有一个更新一些行文的SP,我想更新一下我自己在选择中的立场的SortOrder栏——这里是我的法典的一部分:

UPDATE StagingCategoryItems 
    SET IsDeleted = 0,
    ModifiedOn = GETDATE(),
    ModifiedBy =  DragDropSP  
    WHERE ItemID IN (
    SELECT ItemID
     FROM StagingCategoryItems 
       WHERE ManufacturerID=@ManufacturerID 
       AND CategoryID=@CategoryID
     AND ItemID IN 
     (
    SELECT items
              FROM dbo.Split(@InIds, , )  
        ) 
    AND IsDeleted=1
    )
    AND ManufacturerID=@ManufacturerID AND CategoryID=@CategoryID

因此,我要补充说:

SortOrder = POSITION_IN_SELECT_RESULT

我如何做到这一点?

Answer 1

如果您谈论@InIds中的输入顺序,则可能需要改变这一功能,以便将排名列入产出。假设数字表:

SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 200000;

;WITH n AS
(
    SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.objects AS s1
    CROSS JOIN sys.objects AS s2
    CROSS JOIN sys.objects AS s3
)
SELECT [Number] = rn - 1 INTO dbo.Numbers
FROM n WHERE rn <= @UpperLimit + 1;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

你们可以发挥这样的作用:

CREATE FUNCTION dbo.Split
(
    @List       VARCHAR(MAX),
    @Delimiter  VARCHAR(10)
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT 
            [Position] = ROW_NUMBER() OVER (ORDER BY [Number]),
            [Value] = CONVERT(INT, 
                SUBSTRING(@List, [Number],
                CHARINDEX
                (
                    @Delimiter, @List + @Delimiter, [Number]
                ) - [Number]))
        FROM dbo.Numbers
        WHERE Number <= LEN(@List)
            AND SUBSTRING
            (
                @Delimiter + @List, [Number], LEN(@Delimiter)
            ) = @Delimiter
    );
GO

页: 1 UPDATE可研究类似情况:

UPDATE i SET 
    [Order]    = f.[Position],
    IsDeleted  = 0,
    ModifiedOn = GETDATE(),
    ModifiedBy =  DragDropSP  
FROM dbo.StagingCategoryItems AS i
INNER JOIN dbo.Split(@InIds,  , ) AS f
ON i.ItemID = f.[Value]
WHERE IsDeleted = 1
AND   ManufacturerID = @ManufacturerID 
AND   CategoryID     = @CategoryID;

(我也删除了你对同一表格的多余检查。)

友情链接