[SqlServer] Aiuto con uso di COALESCE

[citrus]

Salve a tutti,
ho scritto una stored procedure che mi permette di estrarre dati da una tabella.
La sp ammette la possibilità di filtrare su uno o più campi tramite l'uso della funzione COALESCE (che ritorna la prima espressione non null tra quelle passate in argomento).

Posto il codice di una sp di esempio:

CREATE PROCEDURE [PRF].[Utenti_READ] 
@Id int = NULL,
@IdSocieta int = NULL,
@Username varchar(MAX) = NULL,
@Password varchar(MAX) = NULL,
@Nome varchar(MAX) = NULL,
@Cognome varchar(MAX) = NULL 

AS BEGIN 
SET NOCOUNT ON; 

SELECT * FROM PRF.Utenti WHERE 1=1 
AND Id = COALESCE(@Id, Id) 
AND IdSocieta = COALESCE(@IdSocieta, IdSocieta) 
AND Username = COALESCE(@Username, Username) 
AND Password = COALESCE(@Password, Password) 
AND Nome = COALESCE(@Nome, Nome) 
AND Cognome = COALESCE(@Cognome, Cognome) 

END

Utilizzata in questo modo la funzione COALESCE mi permette di filtrare su un campo solo se il relativo parametro è diverso da Null.
Se invece il parametro è Null la funzione ritorna il valore della colonna stessa, disabilitando così il filtro.
In questo modo posso chiamare la stored procedure ad esempio passando solo il parametro @Nome ed estraendo così tutti i record con nome ex: "Vattelapesca".

Fin qui tutto benone!

Il problema sorge nel momento in cui ci sono valori Null in tabella.
Se ad esempio ho un record con campo Nome = Null, e chiamo la stored procedure specificando solo il parametro @Id, il risultato della funzione COALESCE per il campo Nome sarà:

WHERE Nome =  NULL

Questo perchè la funzione COALESCE torna Null quando tutti i suoi parametri sono Null (ovviamente) e SqlServer non ammette la sintassi scritta sopra poichè quella corretta sarebbe

WHERE Nome IS  NULL

Dunque i record che presentano valori null per i quali non è stato specificato un valore di ricerca mandano la stored procedure in errore!!

Qualcuno ha idea di come io possa risolvere questo problema?
Posso eventualmente inserire degli IF o dei CASE all'interno della query in modo da valutare la COALESCE e cambiare l'operatore di confronto se questa torna un Null?
Se si qualcuno può farmi un esempio di sintassi perchè io ci ho già provato ma senza successo.

Tenete presente che voglio evitare di creare la query dinamicamente dentro ad una stringa ogni volta perchè il programma che sto scrivendo ha delle criticità a livello di performance e quindi devo spingere al massimo da questo punto di vista.

Vi ringrazio tutti!
citrus

[optime]

ma non ti basta fare

WHERE
(Colonna1=@Colonna1 OR @Colonna1 IS NULL)
...
(ColonnaN=@ColonnaN OR @ColonnaN IS NULL)

???

[citrus]

Originariamente inviato da optime
ma non ti basta fare

WHERE
(Colonna1=@Colonna1 OR @Colonna1 IS NULL)
...
(ColonnaN=@ColonnaN OR @ColonnaN IS NULL)

???

Non funzionerebbe perchè se @Colonna1 fosse NULL avremmo

WHERE
(Colonna1 = NULL OR Colonna1 IS NULL)

La prima parte dell'espressione andrebbe comunque in errore.

[optime]

hai provato?

[citrus]

Originariamente inviato da optime
hai provato?

Si, non funziona, è solo un modo diverso di scrivere quello che avevo scritto io ma il problema rimane ed è che l'operatore '=' usato con 'NULL' restistuisce 'UNKNOWN' (come da manuale).

Sql Server obbliga ad usare l'operatore IS NULL, il che è francamente una s*******a perchè costringe il programmatore a fare ogni volta dei controlli sui dati in tabella!
Mi manca Oracle......

Comunque, avete qualche idea su come si possa risolvere questo problema?

[optime]

ma che vuol dire "NON FUNZIONA"? io lo adopero da sempre e non mi dà errori. posta anche la query, magari c'è qualche cosa da sistemare

[citrus]

Aspetta scusa: mi devo correggere perchè avevo riportato male il tuo esempio:

Si! Funziona!

avevo scritto
WHERE (Colonna1=@Colonna1 OR Colonna1 IS NULL)

invece di
WHERE (Colonna1=@Colonna1 OR @Colonna1 IS NULL)


Il mio problema è che adesso non sono in grado di cercare record con valore NULL in un determinato campo.

Come posso fare secondo te/voi se ad esempio volessi estrarre i record con valore NULL in Colonna1?

Grazie mille
Citrus

[optime]

visto? uomo di poca fede


---


per cercare i NULL devi fare OVVIAMENTE una query diversa (perché logicamente lo scopo è diverso da quello attuale, dove se parametro=NULL il significato è che NON deve cercare)

e nella nuova query usi IS NULL

that easy!

[citrus]

Originariamente inviato da optime
visto? uomo di poca fede


---


per cercare i NULL devi fare OVVIAMENTE una query diversa (perché logicamente lo scopo è diverso da quello attuale, dove se parametro=NULL il significato è che NON deve cercare)

e nella nuova query usi IS NULL

that easy!

mmm impossibile fare tutto con la stessa query vero?

[optime]

tutto è possibile. ma devi in qualche modo far capire alla query che un parametro=null vuol dire DUE cose