Store procedure per cambiare l'ordine di visualizzazione di un record

Questa store procedure T-SQL permette, avendo una tabella con i campi:

- [ID] not null identity(1,) (chiave)
- [pos] not null (ordinamento)

di cambiare l'ordine di visualizzazione delle righe ordinate per il campo [pos] (ORDER BY [pos]).

In questo caso uso la tabella Nortwind.Territories dove ho aggiunto i campi sopra citati.
La store ritorna l'ID del record passato se è stato spostato il record, altrimenti 0

Per eseguirla è sufficiente richiamarla (ad esempio da Query Analyzer o da codice) in questo modo:

SQLCopia

DECLARE @r int
EXEC @r = StoreMoveRecord 2, -1
SELECT @r

Il codice della store:

SQLCopia

/*
 * Avendo una tabella con un campo:
 * - [ID] not null identity(1,) (chiave)
 * - [pos]  not null (ordinamento)
 * permette di cambiare l'ordine di visualizzazione 
 * di una tabella ordinata per il campo [pos]
 * in questo caso uso la tabella Nortwind.Territories 
 * dove ho aggiunto i campi sopra citati.ù
 * ritorna l'ID del record passato se è stato spostato il record, altrimenti 0
 * 02-03-2006 http://www.sgart.it
 */
CREATE PROCEDURE StoreMoveRecord
	@ID int	= null,	-- id dell'elemento da spostare
	@move int = 0	-- se <0 sposta in su, se >0 sposta in giù
 AS

SET NOCOUNT ON
/*
DECLARE @ID int		-- id dell'elemento da spostare
DECLARE @move int	-- se <0 sposta in su, se >0 sposta in giù
SET @ID = 18
SET @move = -1
*/

-- Proc
DECLARE @pos int
DECLARE @IDOther int
DECLARE @posTmp int
SELECT @pos = [pos] FROM Territories WHERE [ID] = @ID

--SELECT @id, @pos

IF @pos is not null
BEGIN
   IF @move > 0
    BEGIN
      -- sposta in giù
      SELECT TOP 1 @IDOther =  [ID]
      FROM Territories
      WHERE [pos] > @pos ORDER BY [pos]
    END
   IF @move < 0
    BEGIN
      -- sposta in su
      SELECT TOP 1 @IDOther =  [ID]
      FROM Territories
      WHERE [pos] < @pos ORDER BY [pos] DESC
    END
   IF @IDOther is not null
    BEGIN
      --SELECT @IDOther AS IDOther
      -- scambia le posizioni
      SELECT @posTmp = [pos]
      FROM Territories
      WHERE [ID] = @ID
      UPDATE Territories SET
         [pos] = (SELECT TOP 1 [pos] FROM Territories WHERE [ID] = @IDOther)
      WHERE [ID] = @ID
      UPDATE Territories SET
         [pos] = @posTmp
      WHERE [ID] = @IDOther
    END
   ELSE
      SET @ID = 0
END
ELSE
   SET @ID = 0

-- ritorna le righe ordinate per posizione (pos)
-- con un campo aggiuntivo che indica il record corrente
SELECT *,
   CASE WHEN [id] = @id THEN '*' ELSE '' END AS  [current]
FROM Territories
ORDER BY [pos]

RETURN IsNull(@ID, 0)