3
I have a Stored Procedure where I need to do a check for a date filter, in case the start date is null I have to do on WHERE take all records less than the final date, otherwise I make a BETWEEN between dates.
If initial date is null I do:
select * from tabela where campoTabela <= @datafim
Otherwise:
select * from tabela where data between @dataInicial and @datafim
2
As it is a stored procedure it is necessary to be attentive to avoid Parameter sniffing, subject explained in a didactic form in the article Introduction to Parameter sniffing in SQL Server.
If index is available in the column data, here is suggestion:
-- código #1
-- gera variáveis internas de limite
declare @xDataInicial date, @xDataFinal date;
IF @datainicial is not null
set @xDataInicial= @datainicial
else
set @xDataInicial= (SELECT top (1) data from tabela order by data);
set @xDataFinal= @datafim;
--
SELECT colunas
from tabela
where data between @xDataInicial and @xDataFinal;
In the above code the variables @xDataInicial and @xDataFinal must be declared of the same data type as in the column data.
0
You can do it like this:
if (@dataInicial is null)
begin
select * from tabela where campoTabela <= @datafim
end
else
begin
select * from tabela where data between @dataInicial and @datafim
end
Check if the parameter is null and according to this makes your select!
Below is demonstration.. when it is null the displayed text is NULL, and when it is not displayed NOT NULL.
NONZERO
NULL
0
If the goal is to execute everything in just one query, you can do it as follows:
SELECT *
FROM tabela
WHERE (@dataInicial IS NULL AND campoTabela <= @datafim)
OR (@dataInicial IS NOT NULL AND data BETWEEN @dataInicial AND @datafim)
By placing the OR can have both conditions in the clause WHERE.
0
You can do it as follows
SELECT *
FROM tabela
WHERE (Data >= @dataInicial AND Data <= @datafim)
OR
(@dataInicial IS NULL AND Data <= @datafim);
Browser other questions tagged sql sql-server date filter
You are not signed in. Login or sign up in order to post.
I tried this way and the performance was very bad, the whole query is a select with 3 sub-selects 3 using Union all I do the variable outside of the first select if it is null executing the query using <= date otherwise do with the between. I made a query only using the first criterion and are returned 30 records, when I applied the second already passed an hour of execution and returned nothing.
– C.Santos
(1) How the column is declared
data? (2) Table has index by columndata?– José Diz