SQL: uso di una lista di valori in una SELECT…WHERE

In questo articolo spiegherò come effettuare una query SELECT…WHERE usando una lista di valori.

E’ abbastanza comune dover effettuare delle query SQL usando una lista di valori anziché un valore singolo.

L’operatore OR

Questa operazione viene effettuata normalmente usando l’operatore OR.

Se ad esempio vogliamo selezionare i clienti che si trovano nelle provincie di Milano e Como, basterà scrivere una query così:

SELECT
   Denominazione, Indirizzo, Localita
FROM
   Clienti
WHERE
   Provincia='MI' OR Provincia='CO'

L’operazione però diventa lunga e tediosa se la lista contiene molti valori: pensate infatti a cosa diventerebbe la query riportata sopra per dieci o più provincie.

L’operatore IN

L’operatore IN dell’istruzione SELECT di SQL Server permette di semplificare notevolmente la scrittura della query.

Tornando al nostro esempio, per selezionare i clienti che si trovano nelle provincie di Milano e Como, basterà riscrivere la query così:

SELECT
   Denominazione, Indirizzo, Localita
FROM
   Clienti
WHERE
   Provincia IN ('MI','CO')

Il motore SQL elaborerà il contenuto della lista inserita tra parentesi e ri-trasformerà la query espandendo la IN in una serie di OR, per poi eseguirla normalmente.

Già da questo breve esempio è facile intuire quanto la stesura della query venga semplificata; e il livello di semplificazione aumenta all’aumentare degli elementi contenuti nella IN.

Ovviamente la stessa sintassi può essere usata anche per valori numerici.

SELECT
   Denominazione, Indirizzo, Localita
FROM
   Clienti
WHERE
   Tipo IN (2,7,12,14,35,36,37)

Uso con Stored Procedures

Nascerà sicuramente la necessità di usare questa sintassi con una Stored Procedure (SP), passando i valori con un parametro.

Immaginiamo allora una SP così:

Create Procedure usp_Cerca_Clienti (@pLista AS VARCHAR(MAX)) AS 
BEGIN
   SELECT
      Denominazione,
      Indirizzo,
      Localita 
   FROM
      Clienti 
   WHERE
      Provincia IN 
      (
         @pLista
      )
END

Qui iniziano subito le difficoltà, già all’atto del richiamo della SP. Avremo infatti delle difficoltà a passare il parametro; se proviamo così:

EXEC usp_Cerca_Clienti @pLista='MI,CO'

la SP svolta diventerà:

SELECT
   Denominazione, Indirizzo, Localita
FROM
   Clienti
WHERE
   Provincia IN ('MI,CO')

che ovviamente non ci darà alcun risultato (non esiste infatti la provincia ‘MI,CO’)

Se invece proviamo così:

EXEC usp_Cerca_Clienti @pLista=''MI','CO''

riceveremo un errore di sintassi.

Anche con valori numerici avremo un errore; infatti se proviamo così:

EXEC usp_Cerca_Clienti_per_Tipo @pLista='1,4'

la SP svolta diventerà:

SELECT
   Denominazione, Indirizzo, Localita
FROM
   Clienti
WHERE
   Tipo IN ('1,4')

restituendoci un errore perché Tipo è una colonna numerica che non può essere raffrontata con un valore stringa.

Emulare un array

Certo, se SQL supportasse gli array, tutto sarebbe più semplice: basterebbe passare un array con i valori da usare nella IN e il gioco sarebbe fatto.

Per emulare il funzionamento di un array possiamo usare una variabile di tipo table: se la prepariamo con una sola colonna avremo emulato un array (monodimensionale).
Però non possiamo usare una variabile table con l’operatore IN: dobbiamo per forza modificare la nostra SP usando una JOIN.

Il ragionamento diventa quindi il seguente:

prepariamo una lista di valori da passare alla SP
i valori saranno separati da un carattere predefinito (in questo esempio useremo una virgola)
passiamo la lista di valori separati da virgola alla SP, in un’unica variabile
la SP elabora la variabile stringa e la trasforma in una variabile table, inserendo ogni valore in una riga
infine mette in JOIN la tabella da selezionare con la variabile table ottenuta prima ed esegue la query

Trasformazione della lista in una table

La prima cosa che dovremo fare sarà quindi di trasformare la nostra lista in una variable table. Per fare questo prepareremo una funzione, così da poterla riusare tutte le volte che vogliamo.

USE [master]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

--The following is a general purpose UDF to split comma separated lists into individual items.
--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.

-- edc 01.SEP.05
-- adattamento da http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

-- edc 20.sep.06
-- adattato per le stringhe

CREATE FUNCTION [dbo].[udf_CodeSplitter]
(
@CodeList varchar(MAX)
)
RETURNS
@ParsedList table
(
   OneCode varchar(10) -- adjust the single element length to fit your needs
)
AS
BEGIN
   DECLARE @Pos int, @OneCode varchar(10) -- adjust the single element length to fit your needs

   SET @CodeList = LTRIM(RTRIM(@CodeList))+ ','
   SET @Pos = CHARINDEX(',', @CodeList, 1)

   IF REPLACE(@CodeList, ',', '') <> ''
   BEGIN
      WHILE @Pos > 0
         BEGIN
            SET @OneCode = LTRIM(RTRIM(LEFT(@CodeList, @Pos - 1)))
            IF @OneCode <> ''
               BEGIN
                  INSERT INTO @ParsedList (OneCode) VALUES (@OneCode)
               END
               SET @CodeList = RIGHT(@CodeList, LEN(@CodeList) - @Pos)
               SET @Pos = CHARINDEX(',', @CodeList, 1)
        END
   END
   RETURN
END

La funzione non fa altro che selezionare i vari elementi ed inserirli in una variabile table; la tabella restituita è formata da una sola colonna (OneCode), e potrà essere messa facilmente in JOIN con la tabella da ricercare.

Per testare il corretto funzionamento della funzione, e per avere un’idea del risultato, basterà eseguire questa query:

SELECT * FROM master.dbo.udf_CodeSplitter('MI,CO')

La Stored Procedure finale

Andremo quindi a sistemare la nostra SP così:

Create Procedure usp_Cerca_Clienti (@pLista AS VARCHAR(MAX))
AS
BEGIN

   SELECT
      Denominazione, Indirizzo, Localita
   FROM
      Clienti INNER JOIN master.dbo.udf_CodeSplitter(@pLista) TJ
      ON Clienti.Provincia=TJ.OneCode

END

mettiamo cioè in JOIN la tabella Clienti con la tabella riportata dalla funzione udf_CodeSplitter, usando come campo di corrispondenza il campo della tabella Cliente dove vogliamo operare la selezione (Clienti.Provincia) e l’unico campo riportato dalla funzione (TJ.OneCode, dove TJ è l’alias della tabella restituita dalla funzione).

L’uso diventa quindi semplicissimo:

exec usp_Cerca_Clienti @pLista='MI,CO'

Gestione delle liste numeriche

Il ragionamento da fare per poter utilizzare delle liste numeriche resta fondamentalmente lo stesso, ma bisogna operare qualche piccola variazione per poter gestire numeri, appunto, anziché stringhe.

Prepareremo quindi una nuova ulteriore funzione:

USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[IDSplitter] Script Date: 02/12/2010 12:26:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

--The following is a general purpose UDF to split comma separated lists into individual items.
--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.

-- edc 01.SEP.05
-- adattamento da http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
--

ALTER FUNCTION [dbo].[udf_IDSplitter]
(
@IDList varchar(MAX)
)
RETURNS
@ParsedList table
(
   OneID int
)
AS
BEGIN
   DECLARE @OneID varchar(10), @Pos int

   SET @IDList = LTRIM(RTRIM(@IDList))+ ','
   SET @Pos = CHARINDEX(',', @IDList, 1)

   IF REPLACE(@IDList, ',', '') <> ''
      BEGIN
         WHILE @Pos > 0
         BEGIN
            SET @OneID = LTRIM(RTRIM(LEFT(@IDList, @Pos - 1)))
            IF @OneID <> ''
               BEGIN
                  INSERT INTO @ParsedList (OneID)
                     VALUES (CAST(@OneID AS int)) --Use Appropriate conversion
               END
               SET @IDList = RIGHT(@IDList, LEN(@IDList) - @Pos)
               SET @Pos = CHARINDEX(',', @IDList, 1)

      END
   END
   RETURN
END

In questo caso la tabella restituita è formata da una colonna (OneID) di tipo numerico (int).

Per testare il corretto funzionamento della funzione, e per avere un’idea del risultato, basterà eseguire questa query:

SELECT * FROM master.dbo.udf_IdSplitter('1,2,4,6,15,22')

Ricordatevi che la lista è un valore stringa, quindi va passata tra apici singoli.

La Stored Procedure finale per i valori numerici

Andremo quindi a sistemare la nostra SP così:

Create Procedure usp_Cerca_Clienti_Tipo (@pLista AS VARCHAR(MAX))
AS
BEGIN

SELECT
   Denominazione, Indirizzo, Localita
FROM
   Clienti INNER JOIN master.dbo.udf_IdSplitter(@pLista) TJ
   ON Clienti.Tipo=TJ.OneId

END

In questo caso mettiamo in JOIN la tabella Clienti con la tabella riportata dalla funzione udf_IdSplitter, usando come campo di corrispondenza il campo della tabella Cliente dove vogliamo operare la selezione (Clienti.Tipo, che è di tipo int) e l’unico campo riportato dalla funzione (TJ.OneId, dove TJ è l’alias della tabella restituita dalla funzione).

L’uso diventa quindi semplicissimo:

exec usp_Cerca_Clienti_Tipo @pLista='2,7,12,14,35,36,37'

Considerazioni finali

Il metodo esposto semplifica notevolmente la stesura delle query di selezione basate su liste di valori. Dato che però sfrutta una funzione e una variabile table, potrebbe risultare poco performante in presenza di liste molto corpose. In questi casi potrebbe risultare più comodo popolare direttamente una tabella temporanea e metterla in JOIN con la tabella da selezionare (magari indicizzando la tabella temporanea, cosa che non può essere fatta con le variabili table).
Uso il condizionale perché le performance sono influenzate da molti fattori, per cui è consigliabile effettuare delle prove caso per caso.

DiCesare