Ordering Stored Procedure

• Brion Johnson

  SSC Journeyman

  Points: 94

  More actions

  February 17, 2004 at 6:09 pm

  #161630

  I want to do the following:

  @NewOrder = SELECT FLD_ORDER FROM TBL_ORDER

  if @NewOrder < 100 then @NewOrder = 100

  else @NewOrder = @NewOrder + 1

  SELECT * FROM TBL_ORDER WHERE FLD_ORDER = -999

  loop through these records and set FLD_ORDER = @NewOrder

  but add 1 to @NewOrder each time

  if @NewOrder = 200 then

  you start with

  A -999

  B -999

  C -999

  D 198

  E 199

  /

  you end up with

  A 200

  B 201

  C 202

  D 198

  E 199

   

  Thanks!

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  February 20, 2004 at 8:56 am

  #494945

  I can't test out an exact solution, as I don't have access to SQL Server at the moment, but you may be able to do what you want with something like this:-

  declare @neworder int

  select @NewOrder = SELECT FLD_ORDER FROM TBL_ORDER

  Update TBL_ORDER set FLD_ORDER = @neworder,@neworder = @neworder + 1 WHERE FLD_ORDER = -999