Condição If no where SQL Server

Question:

I have a Stored Procedure where I need to check for a date filter, if the start date is null I have to do it in the WHERE get all records smaller than the end date, otherwise I do a BETWEEN between the dates.

Example

If the start date is null, I do:

 select * from tabela where campoTabela <= @datafim

If not:

 select * from tabela where data between @dataInicial and @datafim

Answer:

As it is a stored procedure, you must be careful to avoid parameter sniffing , a subject explained in a didactical way in the article Introduction to parameter sniffing in SQL Server .

If there is an index by the data column, here is a 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 code above the @xDataInicial @xDataFinal and variables must be declared in the same type of column data data .

Scroll to Top