Sql how to change query based on parameters

Question

Sorry I don't know how to put it in title, I have this query on an aspx page with 2 datepickers (let's say data and data2)

select utenti.nome as tecnico, Richieste.IDRic as idchia, richieste.descr as rdescr, ISNULL(richieste.assistremota,0) as assremota, Clienti.RagSociale as ragsoc, richieste.descr as descr, richieste.priorita as prior, richieste.tipo as tipo, richieste.rforologio as rforo, ISNULL(statoric.appuntamento,0) as app, ISNULL(statoric.oradalle,0) as dalle, ISNULL(statoric.oraalle,0) as alle, statoric.ID as idstato 
from clienti 
inner join richieste on clienti.idcliente = richieste.rfcliente 
inner join statoric on statoric.rfric = richieste.idric 
inner join stati on stati.idstato = statoric.rfstato 
inner join utenti on utenti.idutente=statoric.rftecnico 
where statoric.attuale = 1 and statoric.rfstato < 14 and statoric.dataass = @data and statoric.rftecnico = 8 order by app desc, oraalle asc, prior desc

I need to change the "statoric.dataass = @data" part to do this (pseudocode):

if data 2 is null then 
    "statoric.dataass = @data"
else
    "statoric.dataass between @data and @data2"
end if

How can I do it? I tried case and If but I'm doing something wrong... thanks

also note between can cause problems if you do use TIME part — jean, Feb 14 '17 at 12:29
BETWEEN is equivalent to data1<= datass<= data2 so i swaped the dates: data1 <= datass AND (data2 is null OR datass <= data2) — jean, Feb 14 '17 at 13:24

score 0 · Accepted Answer · answered Feb 14 '17 at 12:08

0

Try coalesce

... statoric.dataass between @data and coalesce(@data2, @data) ...

answered Feb 14 '17 at 12:08

Serg

22,285
5
21
48

So simple, thanks! I just improved it a bit like this statoric.dataass between @data and coalesce(NULLIF(@data2,''), @data) because I can have both null or empty values – Gabriele Cozzolino Feb 14 '17 at 12:50
I have a problem using this on ASP.NET, seems like it is interpreting it differently from sql server. If I execute the query with statoric.dataass between data and coalesce(NULLIF(data2,''), data) on sql server I get the right result even if data2=null, instead on asp.net it returns nothing (but then it works if I select data2). Any hint? – Gabriele Cozzolino Feb 14 '17 at 13:15
See http://stackoverflow.com/questions/4555935/assign-null-to-a-sqlparameter for example – Serg Feb 14 '17 at 13:23

score 0 · Answer 2 · answered Feb 14 '17 at 12:09

0

Here is one method:

where statoric.attuale = 1 and
      statoric.rfstato < 14 and
      ( (@data2 is null and statoric.dataass = @data) or
         statoric.dataass between @data and @data2
      ) and
      statoric.rftecnico = 8

However, you might consider this:

where statoric.attuale = 1 and
      statoric.rfstato < 14 and
      statoric.dataass >= @data and
      statoric.dataass <= coalesce(@data2, @data) and
      statoric.rftecnico = 8

The advantage of this approach is that it can take advantage of an index on (attuale, rftecnico, dataass).

answered Feb 14 '17 at 12:09

Gordon Linoff

1,242,037
58
646
786

I have a problem using this on ASP.NET, seems like it is interpreting it differently from sql server. If I execute the query with data2=null on sql server I get the right result, instead on asp.net it returns nothing (but then it works if I select data2). Any hint? – Gabriele Cozzolino Feb 14 '17 at 13:21
@GabrieleCozzolino . . . I don't understand why that would be an issue. Could `@data` be `NULL`? Your question doesn't suggest that could be the case. – Gordon Linoff Feb 15 '17 at 03:12
There are 2 datepickers on the page, the query is triggered by date change of each, so if user select data2 first then data can be null and vice versa – Gabriele Cozzolino Feb 16 '17 at 05:16

Sql how to change query based on parameters

2 Answers2