How to reorder row/sequence in sql table using stored procedure

 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