SQLTeam.com | Weblogs | Forums

Put whatever record to different place in table

tsql
sql2014

#1

Hello! I have a huge problem which i cant get to work. I have table where is three columns - CountryRegionCode, Name, ModifiedDate. I have around 200 country records there. The thing i want to be done is have a code which could take record number 140 put to anywhere other row, that could be to 100 - user input. And i need to make Stored Procedure for that to let the users input the new row place where the record would be. This is called sorting. If you have some idea let me know! Thank youuu!


#2

You don't insert into a specific place. If you need a specific sort order, include that as a field and then you can update the sort order column as needed when inserting a new row. So, if the user selects that the sort order field for the new record should be 140 then you can update the rest by

Update TableName Set SortOrder = SortOrder + 1
WHERE SortOrder >= @NewSortOrder

@NewSortOrder would be the variable passed from the user for the sort order value for that new row.


#3

What can be done here is you can add another column , Sort Number, and let user input be stored there for that particular row.
Suppose for record 140 if they want order 100 , update sort number to 100.
and while showing result. sort your data with SortNumber.

you can understand sort number to be like an ID .