How to reorder row/sequence in sql table using stored procedure
CREATE TABLE [dbo].[ConfigureMasterFields](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FieldsName] [varchar](128) NOT NULL,
[IsRequired] [bit] NOT NULL,
[Sequence] [int] NULL,
[IP] [nchar](10) NULL,
[FormId] [int] NULL
CONSTRAINT [PK_ConfigureMasterFields] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Proc [dbo].[spAutoReorderingRow] -- exec spAutoReorderingRow 36,1,12
(
@Id int,
@Pos int,
@FormId int
)
as
begin
SET NOCOUNT ON;
--UPDATE ConfigureMasterFields SET Sequence = @Pos WHERE Id = @Id
MERGE INTO ConfigureMasterFields T USING
(
SELECT ROW_NUMBER() OVER(ORDER BY Sequence) AS Position, Id FROM ConfigureMasterFields Where FormId=@FormId
) S
ON T.Id = S.Id
WHEN MATCHED THEN UPDATE SET Sequence = S.Position;
------
DECLARE @newPosition INT = @Pos; -- and its new position
DECLARE @oldPosition INT;
SELECT
@FormId = FormId,
@oldPosition = Sequence
FROM ConfigureMasterFields
WHERE Id = @Id;
UPDATE ConfigureMasterFields SET Sequence = CASE
WHEN Id = @Id THEN @newPosition -- item being moved gets new position without question
WHEN @oldPosition < @newPosition THEN Sequence - 1 -- item moved up, other items must be pushed down
WHEN @oldPosition > @newPosition THEN Sequence + 1 -- item moved down, other items must be pushed up
END
WHERE FormId = FormId AND Sequence BETWEEN -- only update items with same Plan_Id and position between old and new pos
CASE WHEN @oldPosition < @newPosition THEN @oldPosition ELSE @newPosition END AND
CASE WHEN @oldPosition > @newPosition THEN @oldPosition ELSE @newPosition END;
----
end
