Как вставить строки и переместить остальные вниз на основе номера порядка сортировки?

Question

Как создать процедуру, которая вставляет запись в таблицу по порядку сортировки (столбец ранжирования), а остальные перемещает вниз. Например, мой sp получает входные данные PhysID(FK), UserID(FK),UserRoleID(FK), которые имеют ограничение, что они должны быть уникальной комбинацией, и последний вход, SortOrder, который определяет, где комбинация вставлена ​​в таблица и каждая запись после этого порядка сортировки перемещаются вниз (или добавляют 1 к порядку сортировки), чтобы освободить место для вставленной строки.

Есть три варианта ролей на физика. Каждому идентификатору пользователя разрешено заполнять любую из 3 ролей, но не более одной на физика. Каждый ролид имеет резервные идентификаторы на физ. Когда идентификатор пользователя используется для какой-либо роли с определенным физическим именем, этот идентификатор больше не является кандидатом на выбор.

После этого, как я могу вставить записи в соответствии с порядковым номером сортировки, который обновляет все записи с одинаковым или более высоким порядком сортировки, добавляя 1, перемещая их вниз в порядке сортировки.

Я очень новичок, так что не бей меня слишком сильно. Вот мой код

CREATE PROCEDURE dbo.SP_InsertRole 
    @PhysID int,
    @UserID int,
    @UserRoleID tinyint,
    @SortOrder int
AS
BEGIN TRANSACTION
    BEGIN TRY
    BEGIN   
        UPDATE dbo.Physicians_Users
        SET SortOrder = SortOrder + 1
        WHERE @PhysID = PhysicianID AND UserRoleID = @UserRoleID AND @SortOrder >= @SortOrder
    END
    BEGIN
        SELECT @UserID = UserID FROM dbo.Users 
        IF not exists(SELECT UserID FROM dbo.Physicians_User WHERE PhysiciansID = @PhysID AND @UserID = UserID)
        INSERT INTO dbo.Physicians_Users(PhysicianID,UserID,UserRoleID,SortOrder)
        VALUES(@PhysID,@UserID,@UserRoleID,@SortOrder)
    END
    COMMIT TRANSACTION
    END TRY
BEGIN CATCH
    SELECT @@ERROR
    ROLLBACK TRANSACTION
END CATCH

RETURN 0 

GO

Answer 1

Попробуйте этот SP

CREATE PROCEDURE dbo.SP_InsertRole 
@PhysID int,
@UserID int,
@UserRoleID tinyint,
@SortOrder int
AS
BEGIN TRANSACTION
  BEGIN TRY                    
    IF NOT EXISTS(
                  SELECT 1
                  FROM dbo.Physicians_User
                  WHERE PhysiciansID = @PhysID AND UserRoleID = @UserRoleID
                    AND UserID = @UserID
                  )
    BEGIN
      INSERT INTO dbo.Physicians_Users(PhysicianID, UserID, UserRoleID, SortOrder)
      VALUES (@PhysID, @UserID, @UserRoleID, @SortOrder)

      UPDATE dbo.Physicians_Users
      SET SortOrder = SortOrder + 1
      WHERE @PhysID = PhysicianID AND UserRoleID = @UserRoleID
        AND SortOrder >= @SortOrder
    END

    COMMIT TRANSACTION
  END TRY
BEGIN CATCH
  SELECT @@ERROR
  ROLLBACK TRANSACTION
END CATCH

RETURN 0 

GO

Answer 2

Попробуйте эти версии запроса. Я думаю, что вы путаете скалярные аргументы функции с именами столбцов:

BEGIN   
    UPDATE dbo.Physicians_Users
        SET SortOrder = SortOrder + 1
        WHERE @PhysID = PhysicianID AND UserRoleID = @UserRoleID AND
              SortOrder >= @SortOrder;   -- <-- this is the line that changed
END
BEGIN
    SELECT @UserID = UserID FROM dbo.Users;
    IF not exists(SELECT UserID
                  FROM dbo.Physicians_User
                  WHERE PhysiciansID = @PhysID AND @UserID = UserID)
    INSERT INTO dbo.Physicians_Users(PhysicianID,UserID,UserRoleID,SortOrder)
        VALUES(@PhysID,@UserID,@UserRoleID,@SortOrder)
END

Это исправляет синтаксис. Вы также можете выразить insert без if при выполнении:

    INSERT INTO dbo.Physicians_Users(PhysicianID,UserID,UserRoleID,SortOrder)
        select @PhysID, @UserID, @UserRoleID, @SortOrder
        where not exists(SELECT UserID
                  FROM dbo.Physicians_User
                  WHERE PhysiciansID = @PhysID AND @UserID = UserID)

Логика все еще не совсем верна. Если запись уже есть в таблице, зачем обновлять sortorder, И, если запись находится в таблице, вам, вероятно, следует обновить порядок сортировки для этой записи.