Store procedure per estrarre righe casualmente

Questa store procedure T-SQL (per SQL Server 2000) permette di estrarre delle righe casualmente da una tabella (nell'esempio presentato Products del database Northwind).

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

SQLCopia

exec StoreGetRandomRowsFromTable 11

dove il numero 11 rappresenta il numero di righe ritornate.

Il codice della store è il seguente:

SQLCopia

/*
 * Estrae casualmente delle righe da una tabella 
 * (in questo caso Northwind.Products)
 * 02-03-2006 http://www.sgart.it
 */
CREATE PROCEDURE StoreGetRandomRowsFromTable
	@numRow int		-- numero di righe che deve restituire
AS
/*
DECLARE @numRow int		-- numero di righe che deve restituire
SET @numRow = 15
*/

SET NOCOUNT ON
-- creo una tabella temporanea per contenere tutti gli id della tabella
-- da cui voglio ottenere dei valori random (sorgente)
DECLARE @TabAllID  TABLE (
	[pos] int not null IDENTITY(1,1),
	[ID] int not null
)
-- tabella che conterrà tutti gli id generati random
DECLARE @TabRnd  TABLE (
	[ID] int not null
)

DECLARE @maxPos int	-- numero massimo di elementi nella tabella temporanea
DECLARE @r int		-- numero di riche estratte
DECLARE @i int		-- numero di cicli effettuati
DECLARE @iMax int	-- numero massimo di cili nel caso non estragga numeri

-- estraggo tutti gli id della tabella sorgente
INSERT @TabAllID
SELECT ProductID
FROM Products
SET @maxPos = @@ROWCOUNT		-- numero totale di righe inserite

DECLARE @IDRand int

SET @iMax = @numRow * 3
SET @i = 0
SET @r = 0
WHILE @r < @numRow AND @i < @iMax
 BEGIN
   SELECT @IDRand = [ID]
   FROM @TabAllID
   WHERE [pos] = CAST(1 + (RAND() * @maxPos) AS int)

   IF (@IDRand is NOT NULL)
      AND (NOT EXISTS(SELECT [ID] FROM @TabRnd WHERE [ID] = @IDRand))
    BEGIN
      INSERT @TabRnd VALUES(@IDRand)
      SET @r = @r + 1	
    END
   SET @i = @i +1
END 

-- ritorna la tabella con le righe scelte casualmente
SELECT * 
FROM Products
WHERE ProductID IN (SELECT [id] FROM @TabRnd) 
ORDER BY [ProductName]

RETURN @r

se la tabella contiene meno righe di quelle richieste il valore ritornato coincide con il numero righe selezionate.