Molto spesso mi è stato chiesto se fosse possibile visualizzare il numero progressivo di riga del resulset di una stored procedure o di una generica select.
Facciamo un esempio molto banale:
Sia data una tabella contenente due campi, codice e descrizione voglio ottenere un terzo campo contenente il numero progressivo delle righe:
Codice, Descrizione, [NumRiga]
Una prima soluzione potrebbe essere quella di aggiungere alla tabella un campo indentity con incremento automatico, in modo che tutte le righe abbiano già in partenza il numero progressivo, ma ovviamente tale soluzione non va bene quando parliamo di query su più tabelle o quando la nostra tabella subisce delle cancellazioni; in tal caso la numerazione avrebbe dei “buchi” e noi non vogliamo tutto questo.
Molto probabilmente la nostra tabella in analisi, non ha un campo “NumRiga” né questo campo ha un incremento automatico.
Da scartare anche la possibilità di aggiungervelo a posteriori, anche perché, se la tabella subirà cancellazioni, avremo certamente dei buchi nella progressione.
Vediamo allora cosa si può fare:
USE TEMPDB
-- Definisco la tabella myTable
CREATE TABLE myTable
(
Codice varchar(6) NOT NULL,
Descrizione varchar(25) NOT NULL
) ON [PRIMARY]
GO
-- La popolo
SET NOCOUNT ON
INSERT dbo.myTable VALUES('SQL2K', 'SQL Server 2000')
INSERT dbo.myTable VALUES('SQL2K5', 'SQL Server 2005')
INSERT dbo.myTable VALUES('VB6', 'Visual Basic 6')
INSERT dbo.myTable VALUES('VB2K5', 'Visual Basic 2005')
INSERT dbo.myTable VALUES('VB2K5E', 'Visual Basic 2005 Express')
SET NOCOUNT OFF
GO
-- Effettuo la select generica
SELECT
Codice,
Descrizione
FROM myTable
GO
/* Output:
Codice Descrizione
--------- ----------
SQL2K SQL Server 2000
SQL2K5 SQL Server 2000
VB6 Visual Basic 6
VB2K5 Visual Basic 2005
VB2K5E Visual Basic 2005 Express
(5 row(s) affected)
*/
Diciamo che ci possono essere due varianti al problema, una che si basa direttamente su una tabella temporanea, l’altra che utilizza una forma un po’ più pulita che è quella della stored procedure.
------------------------------
-- VARIANTE 1
-- Viene creata una tabella di appoggio
-- contente i valori originali e il nuovo campo
-- NumRiga autoincrementale
------------------------------
CREATE TABLE #tmp
(
Codice varchar(6) NOT NULL,
Descrizione varchar(25) NOT NULL,
NumRiga int NOT NULL IDENTITY (1, 1),
) ON [PRIMARY]
GO
-- Riempio la tabella temporanea con i dati della mia tabella di partenza
INSERT INTO #tmp
SELECT codice, descrizione FROM myTable
-- Per concludere eseguo la mia select sulla nuova tabella
SELECT * FROM #tmp
------------------------------------------------------
/* Output:
Codice Descrizione NumRiga
--------- ---------- -------
SQL2K SQL Server 2000 1
SQL2K5 SQL Server 2000 2
VB6 Visual Basic 6 3
VB2K5 Visual Basic 2005 4
VB2K5E Visual Basic 2005 Express 5
(5 row(s) affected)
*/
------------------------------------------------------
-- Rimuovo le tabelle
DROP TABLE #tmp
GO
Vediamo ora la seconda variante.
Io personalmente la preferisco alla prima perché mi da più libertà di movimento e, soprattutto, mi permette da client di richiamare una sola routine.
------------------------------
-- VARIANTE 2
-- Qualora la mia tabella avesse una chiave univoca,
-- una variante interessante potrebbe essere quella di creare
-- la tabella temporanea con solo il campo contatore ed il campo
-- “chiave” della tabella da analizzare.
-- Il tutto potrebbe essere inserito in una stored procedure,
-- rendendo più compatto e semplice da gestire il codice.
------------------------------
CREATE PROCEDURE TabellaSelectCount
AS
CREATE TABLE #tmp
(
NumRiga int NOT NULL IDENTITY (1, 1),
Codice varchar(6) NOT NULL,
) ON [PRIMARY]
-- Riempio la tabella temporanea con solo il codice
INSERT INTO #tmp
SELECT codice FROM myTable
SELECT
N.Codice,
N.Descrizione,
T.NumRiga
FROM myTable N
LEFT JOIN #tmp T ON T.Codice = N.Codice
-- Elimino la tabella temporanea --
DROP TABLE #tmp
GO
-- Faccio la chiamata alla sp
EXEC TabellaSelectCount
------------------------------------------------------
/* Output:
Codice Descrizione NumRiga
--------- ---------- -------
SQL2K SQL Server 2000 1
SQL2K5 SQL Server 2000 2
VB6 Visual Basic 6 3
VB2K5 Visual Basic 2005 4
VB2K5E Visual Basic 2005 Express 5
(5 row(s) affected)
*/
------------------------------------------------------
-- Rimuovo la tabella dei dati e la relativa sp
DROP PROCEDURE TabellaSelectCount
DROP TABLE myTable
Alcune osservazioni
In un dbms tale numerazione è non ha senso ed è fondamentalmente inutile, dato che non è importante sapere la posizione progressiva di un record, ma piuttosto reperire il record stesso!