A volte può capitare di dover scrivere applicazioni che diano all'utente finale la possibilità di eseguire ricerche sempre più approfondite e dettagliate.
Molto spesso il cliente ha bisogno di filtrare quasi per intero i dati inseriti in una tabella.
Ad esempio, pensando ad una biblioteca, potremmo voler filtrare l'anagrafica dei libri sia per titolo, che per autore, che per categoria, che per tanti altre informazioni disponibili.
Sia lato applicazione, sia lato database, risulta scomodo gestire con tanti "if" la creazione del criterio di filtro.
Grazie ad un mio collega, Marco Rossi (che si chiama così davvero, non è un'identità generica  ) si è arrivati ad utilizzare una tecnica che riduce lo spreco di codice.
Ma prendiamo l'esempio della biblioteca. Andiamo a creare una tabella e ad inserirvi dati concreti.

 
CREATE TABLE Books
(
 Id int identity (1,1) primary key,
 [Name] varchar(100),
 Description varchar(1000),
 IdCategory int,
 IdAuthor int,
 IdPublisher int,
 Pages smallint
)
INSERT Books
SELECT 'SQL Server for dummies','Un libro per chi è alle prime armi con SQL Server', 1, 1, 1, 150
INSERT Books
SELECT 'Inside SQL Server: T-SQL Programming','Un libro per programmare in T-SQL', 1, 2, 2, 300
INSERT Books
SELECT 'Inside SQL Server: T-SQL Querying','Un ibro per ottimizzare le query', 1, 2, 2, 300
INSERT Books
SELECT 'Le cronache del mondo emerso - Vol.1','Un fantasy italiano', 1, 3, 3, 400
INSERT Books
SELECT 'Le cronache del mondo emerso - Vol.2','Un fantasy italiano', 1, 3, 3, 400
 
Una volta fatto questo, ipotizziamo di creare un'applicazione utile a ricercare dei libri in una biblioteca virtuale. L'utente, potrà scegliere di filtrare per Nome, Categoria, Autore, Casa editrice e numero di pagine.
Una delle soluzioni potrebbe essere quella di creare un SQL dinamico in base ai parametri valorizzati nell'applicativo. Ma sappiamo che è preferibile evitare l'ausilio dell'esecuzione di stringhe dinamiche in SQL..
Di conseguenza, proviamo a procedere come segue..
Creiamo una stored procedure che si aspetta i parametri di filtro:

CREATE PROCEDURE GetBooks
(
 @Name varchar(100) = NULL,
 @Category int = NULL,
 @Author int = NULL,
 @Publisher int = NULL,
 @Pages smallint = NULL
)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT
  *
 FROM
  Books
 WHERE
  (@Name IS NULL OR [Name] = @Name) AND
  (@Category IS NULL OR IdCategory = @Category) AND
  (@Author IS NULL OR IdAuthor = @Author) AND
  (@Publisher IS NULL OR IdPublisher = @Publisher) AND
  (@Pages IS NULL OR Pages = @Pages)
END
 
Notiamo che tutti i parametri, sono impostati con default a NULL, in modo tale che l'applicazione non sia obbligata a passare valori.
Cosa succede con una tecnica di questo tipo? Se un criterio (ad esempio il valore di un TextBox o di un DropDownList) non è indicato, il parametro non viene passato alla chiamata della stored procedure ed i dati relativi a quel parametro non sono filtrati. Se tutti i parametri non sono passati, otteniamo tutto il resultset, viceversa, otteniamo i dati filtrati per ogni parametro indicato.
Ovviamente è possibile fare in modo che, se non si seleziona alcun criterio, il resultset sia vuoto, ma questo rimane a discrezione dello sviluppatore che decide di utilizzare la tecnica.
I vantaggi che conseguono sono vari:
- Innanzitutto ci si sposta sul database, lasciando l'applicazione sgombra di codice impuro e addirittura Hard Coded.
- Essendo all'interno di una stored procedure, l'accesso ai dati è più veloce, poichè il piano di esecuzione è già pronto sul database.
- Il traffico di righe di codice relativo alla chiamata della stored procedure è molto ridotto, in quanto si tratta di una semplice EXEC.
- Non servono casistiche per comporre il filtro. La select è una sola e la where pure.
- Non appesantisce il piano di esecuzione della query

Uno svantaggio che può emergere è quello di non avere un SQL leggerissimo, poichè ingrandisce all'aumentare dei filtri definiti.
Ovviamente, per ottenere prestazioni elevate è necessario definire correttamente gli indici sulla/e tabella/e.

Ecco l'esempio:

-- Tutti

exec GetBooks

 

-- ricerca per nome

exec GetBooks     @Name = 'SQL Server for dummies'

 

-- ricerca per numero di pagine

exec GetBooks     @Pages = 300

 

-- ricerca per tutti i valori

exec GetBooks     @Name = 'SQL Server for dummies',@Category = 1, @Author = 1, @Publisher = 1, @Pages = 150

 
Ed ecco i resultset: