About sql server stored procedure for updating serial no column

Question

Hello everyone
I have one integer column of serial no in sql server table.
I am inserting values in it by incrementing from last one by 1
I want query such as
when i delete any one record from dot net front end then sr nos greater than that no should get decrease by one
Hope you understand

please help

Jaganathan Bantheswaran · Answer

Hi Create a stored proc which contains the below code -- Get the number of rows in the looping table DECLARE @RowCount INT SET @RowCount = (SELECT COUNT(SN) FROM @myTable where SN > @serialNumber) -- Declare an iterator DECLARE @I INT -- Initialize the iterator SET @sn = @serialNumber + 1; SET @I = 1; -- Loop through the rows of a table @myTable WHILE (@I BEGIN UPDATE @myTable SET SN = @sn - 1 where SN = @sn SET @sn = @sn + 1 -- Increment the iterator SET @I = @I + 1 END

Suthish Nair · Answer

Mayur are you sure you don't get duplicate records? Why don't have a soft delete logic instead of deleting a record?

Mayur Gujrathi · Answer

thanks jagannath i did it as follows CREATE PROCEDURE [dbo].[YourSP](@Serialnum INT ) AS BEGIN DELETE FROM YourTable WHERE Serial_num = @Serialnum UPDATE YourTable SET Serial_num = Serialnum - 1 WHERE Serial_num > @Serialnum END