If no where condition SQL Server

As a stored procedure one must be careful to avoid parameter sniffing .

If there is an index for the data column, this 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 above code the variables @x InitialD and @xDataFinal must be declared of the same data type of column data .

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 putting OR you can have both conditions in the WHERE clause.

You can do 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

You check if the parameter is null and according to this makes your select!

Below is a demonstration. When it is null , the displayed text is NULL, and when it is not it is NOT NOT NULL.

NOT NULL

NULL

You can do this as follows

SELECT *
FROM tabela
WHERE (Data >= @dataInicial AND Data <= @datafim) 
       OR 
      (@dataInicial IS NULL AND Data <= @datafim);