Ignorando un parametro NULL in T-SQL

Voglio essere in grado di passare un elenco di parametri e ignorare quelli che sono NULL. In questo modo, la query sta facendo finta che il filtro non sia presente e lo ignori.

Lo stavo facendo in questo modo:

(@thing IS NULL or [email protected]) 

È giusto, e se sì, si comporterebbe male? Sembra essere molto più lento rispetto alla costruzione separata di SQL.

Qual è il modo ottimale per farlo?

FISSO! Vedi la risposta di Marc Gravell. In sintesi usando IS NULL molte volte è un grande successo per le prestazioni.

Una volta ottenuto più di un paio di questi, allora sì: inizia a diventare piuttosto lento. In questi casi, tendo ad usare TSQL generato – es

 DECLARE @sql nvarchar(4000) SET @sql = /* core query */ IF @name IS NOT NULL SET @sql = @sql + ' AND foo.Name = @name' IF @dob IS NOT NULL SET @sql = @sql + ' AND foo.DOB = @dob' // etc EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime', @name, @dob 

eccetera

Si noti che sp_ExecuteSQL memorizza nella cache i piani di query, quindi qualsiasi query con gli stessi argomenti può potenzialmente riutilizzare il piano.

Lo svantaggio è che, a meno che non si firmi SPROC, il chiamante abbia bisogno delle autorizzazioni SELECT sulla tabella (non solo le autorizzazioni EXEC sul SPROC).

Lo gestirò in questo modo.

 WHERE Thing = ISNULL(@Thing, Thing) 

Se stai usando il parametro come filtro sulla clausola where, funzionerà molto bene. Ignorerà il parametro se è nullo.

Io generalmente uso

 WHERE (id = @id OR @id IS NULL) AND (num = @num OR @num IS NULL) 

eccetera.

Non sono sicuro che sia il modo “ottimale”, ma questo è esattamente ciò che faccio nelle mie stored procedure per gli stessi scopi. Il mio istinto è che questo è più veloce di una query creata dynamicmente dal punto di vista del piano di esecuzione. L’altra opzione è quella di creare una query per ciascuna combinazione di questi “flag” che stai trasmettendo, ma in realtà non è così scalabile.

Una tecnica che ho usato in passato per questo scenario è di utilizzare la funzione COALESCE come parte della mia clausola WHERE. I libri online forniranno informazioni più approfondite sulla funzione, ma ecco un frammento di come puoi usarlo nello scenario che hai descritto:

 create procedure usp_TEST_COALESCE ( @parm1 varchar(32) = null, @parm2 varchar(32) = null, @parm3 int = null ) AS SELECT * FROM [TableName] WHERE Field1 = COALESCE(@parm1, Field1) AND Field2 = COALESCE(@parm2, Field2) AND Field3 = COALESCE(@parm3, Field3) 

La funzione COALESCE restituirà la prima espressione non nulla dai suoi argomenti. Nell’esempio sopra, se uno qualsiasi dei parametri è nullo, la funzione COALESCE utilizzerà il valore nel campo sottostante.

Un avvertimento importante all’utilizzo di questa tecnica è che i campi sottostanti nella tabella (che costituiscono la clausola where) devono essere non annullabili.

Guarda il seguente link nella sezione intitolata “The Case Study: Searching Orders”. Questo esplora tutte le opzioni in profondità e dovrebbe darti una panoramica eccellente dei costi associati a ciascuna di queste opzioni. Attenzione, stai molto attento quando usi COALESCE, potrebbe non restituire ciò che pensi che sia.

Saluti,

Tim

Questo è il metodo che uso di solito. Non vedo alcuna ragione perché sia ​​inefficiente, in quanto l’istruzione dovrebbe cortocircuitare su true se @thing è nullo e quindi non richiederebbe una scansione della tabella. Hai qualche prova che questo confronto stia rallentando la tua ricerca? In caso contrario, non mi preoccuperei di ciò.

quando si dichiarano i parametri se si imposta un valore come NULL nel caso in cui non è necessario passare un valore in essi a meno che, ovviamente, non sia necessario. Io uso questa capacità per segnalare se un’altra query deve essere eseguita è casi speciali quando il parametro non è nullo

Di solito lo controllo in questo modo

Il campo IF È NULL

Grazie, questo è stato utile. Ho deciso di utilizzare il metodo sp_ExecuteSQL a causa dei potenziali vantaggi prestazionali menzionati. Ho un approccio leggermente diverso che potresti trovare utile.

 DECLARE @sql nvarchar(4000) DECLARE @where nvarchar(1000) ='' SET @sql = 'SELECT * FROM MyTable' IF @Param1 IS NOT NULL SET @where = @where + ' AND Field1 = @Param1' IF @Param2 IS NOT NULL SET @where = @where + ' AND Field2 = @Param2' IF @Param3 IS NOT NULL SET @where = @where + ' AND Field3 = @Param3' -- Add WHERE if where clause exists, 1=1 is included because @where begins with AND IF @where <> '' SET @sql = @sql + ' WHERE 1=1' + @where --Note that we could also create order parameters and append here SET @sql = @sql + ' ORDER BY Field1'