Contact Us

Use the form on the right to contact us.

You can edit the text in this area, and change where the contact form on the right submits to, by entering edit mode using the modes on the bottom right. 


Oak Ridge, TN, 37830
United States

Sql-Sangria

Re-Order Method

Wade Cantley

The Re-Ordered Russian

3/4 ounce green Creme de Menthe
3/4 ounce Creme de Cacao
3 ounces milk
Swirl the martini glass with chocolate syrup
Shake over ice, strain into a martini glass
Garnish with chopped Andes mints or chocolate shavings


The goal is to re-order a list based on the new position and the old position.

-- incoming vars
DECLARE @InventoryPackageFeatureId varchar(36)
, @OrderNumber INT
, @InventoryPackageId VARCHAR(36)
, @oldOrderNumber INT

SET @InventoryPackageFeatureId = '4029D0E0-8188-4F2F-A9D5-1446BF0FDC3C'
SET @OrderNumber = 2


SELECT @oldOrderNumber = InventoryPackageFeatureOrder
FROM InventoryPackageFeatures
WHERE InventoryPackageFeatureId = @InventoryPackageFeatureId

-- Depending on the direction a record will go, the orderNumber may need to increment for the rest
-- of the processes to work right.
IF @oldOrderNumber < @OrderNumber
  BEGIN
    SET @OrderNumber = @OrderNumber + 1
  END


-- Get the InventoryPackageId
SELECT @InventoryPackageId = TIPF.InventoryPackageId
FROM InventoryPackageFeatures TIPF
WHERE TIPF.InventoryPackageFeatureId = @InventoryPackageFeatureId


-- Increase Order for all numbers greater than the one being changed.
UPDATE InventoryPackageFeatures
SET InventoryPackageFeatureOrder = InventoryPackageFeatureOrder + 1
WHERE InventoryPackageId = @InventoryPackageId
AND InventoryPackageFeatureOrder >= @orderNumber

  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder


-- Now that the order has made room for the new entry, set the new order number for the target record
UPDATE InventoryPackageFeatures
SET InventoryPackageFeatureOrder = @OrderNumber
WHERE InventoryPackageFeatureId = @InventoryPackageFeatureId

-- At this point, depending on how many are in the list, there may be a hole in the order.
-- particularly if your moving an existing the middle of order list


  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder

-- Use this new resulting order within a table update
UPDATE TIPF
    SET InventoryPackageFeatureOrder = TIPF2.newOrder
    FROM InventoryPackageFeatures TIPF
	  INNER JOIN (-- This creates an order number field
						SELECT ROW_NUMBER() OVER(ORDER BY TIPF1.InventoryPackageFeatureOrder) AS newOrder,
		        * FROM InventoryPackageFeatures TIPF1
            WHERE TIPF1.InventoryPackageId = @InventoryPackageId
						) TIPF2
		ON TIPF.InventoryPackageFeatureId = TIPF2.InventoryPackageFeatureId





  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder