Passing NULL date parameters in SQL Server

Question

I'm trying to write a SQL query that retrieves all records from a table, but I also want to be able to filter the results by date range. I'm using stored procedures to execute the query, and I'm passing date parameters to the stored procedure. However, I'm having trouble passing NULL values for the date parameters. I've tried using various methods, such as setting the parameter to NULL or using the ISNULL() function, but I'm still getting errors.

I'm using SQL Server 2019. Is there a way to pass NULL date parameters in SQL Server?
"I'm using the BETWEEN clause to filter records by date range. This is the code I have written so far."

-- Declaración de variables para la paginación
DECLARE @Numero_Pagina INT = 1, @Filas_x_Pagina INT = 3;
-- Declara variables para filtrar por rango de fechas
DECLARE @Fecha_Inicio DATE, 
@Fecha_Fin DATE,
 --Declara variables para poder filtrar por el Id del proveedor para que haga select de todos o de uno que se especifique
@IdProveedor CHAR = NULL, --'PRO-0000001', -- NULL significa todos los proveedores
 ----Declara variables para poder filtrar por el tipo de documento de compra
@TipoDocCompra VARCHAR(50) = NULL, -- NULL significa todos los tipos de documento
 ----Declara variables para poder filtrar por la modalidad de pago
@ModalidadPago VARCHAR(50) = NULL; -- NULL significa todas las modalidades de pago-- Consulta para obtener las compras y sus detalles
WITH Compras AS (
    SELECT 
        MC.IdMaestroCompra, -- No se va a mostrar
        MC.NumeroCompra, -- Este es el correlativo de la compra de la base de datos
        MC.NroDocumento, -- El número de documento de la compra, puede ser la factura o boleta
        MC.FechaCompra,
        P.RazonSocial,
        P.Contacto,
        P.RucNit,
        P.TelContacto,
        MC.TotalCompra,
        MC.ModalidadPago,
        MC.TipoDocCompra,
        U.NombreUsuario,
        ROW_NUMBER() OVER (ORDER BY MC.IdMaestroCompra) AS RowNum
    FROM 
        dbo.MaestroCompra AS MC
    INNER JOIN 
        dbo.DetalleCompra AS DC ON MC.IdMaestroCompra = DC.IdMaestroCompra
    INNER JOIN 
		dbo.Proveedor AS P ON MC.IdProveedor = P.IdProveedor
	INNER JOIN 
		dbo.Usuarios AS U ON MC.IdUsuario = U.IdUsuario
        WHERE 
		Convert(date,MC.FechaCompra) BETWEEN @Fecha_Inicio AND @Fecha_Fin
		AND (@IdProveedor IS NULL OR MC.IdProveedor = @IdProveedor)
		AND (@TipoDocCompra IS NULL OR MC.TipoDocCompra = @TipoDocCompra)
		AND (@ModalidadPago IS NULL OR MC.ModalidadPago = @ModalidadPago)
)
-- crea una condicion para la paginación
SELECT 
	IdMaestroCompra,
	NumeroCompra,
	NroDocumento,
	FechaCompra,
	RazonSocial,
	Contacto,
	RucNit,
	TelContacto,
	TotalCompra,
	ModalidadPago,
	TipoDocCompra,
	NombreUsuario
    FROM
    Compras
    WHERE
    RowNum BETWEEN (@Numero_Pagina - 1) * @Filas_x_Pagina + 1 AND @Numero_Pagina * @Filas_x_Pagina;
    GO
-- Consulta para obtener el número total de compras
SELECT COUNT(*) AS TotalCompras FROM dbo.MaestroCompra;

"I am also attaching the form that I have created for data entry. Once all the data has been entered, users will be able to apply various filters to the data as needed."

"I would also be grateful if you could offer your insights on the pagination method I am currently employing and suggest any more efficient approaches that might be available."

"I am encountering an error and require assistance in resolving it. The error appears to be related to the BETWEEN clause, which is not accepting null values. I would appreciate any guidance you can provide on how to address this issue."

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

I have two recommendations for addressing this issue:

You can utilize default values for null states. For instance, you can check if @Fecha_Inicio is null and assign a default datetime value accordingly. The same approach can be applied to @Fecha_Fin.

1. Alternatively, you can refactor the BETWEEN clause with a new structure like this:

      WHERE
      (@Fecha_Inicio IS NULL OR Convert(date, MC.FechaCompra) >= @Fecha_Inicio)
      
      AND (@Fecha_Fin IS NULL OR Convert(date, MC.FechaCompra) <= @Fecha_Fin)
      
      AND (@IdProveedor IS NULL OR MC.IdProveedor = @IdProveedor)
   

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Answer 2

What data type is Fecha_Compra? Generally, you want to avoid conversions of columns in operations, as that is likely to preclude use of indexes?

Passing NULL to date parameters, is not different from any other data type. You just pass them. But you need to keep in mind that all comparisons with NULL yields UNKNOWN in the three-valued of logic of SQL.

The easiest way to deal with this is to skip BETWEEN altogether and use >= and <=. Then you can handle the parameters just like the other.

As for paging, you can also use OFFSET-FETCH with ORDER BY:

SELECT * 
FROM   sys.objects
ORDER BY object_id
OFFSET  20 ROWS
FETCH NEXT 20 ROWS ONLY

But you should also ask yourself if you really want paging. Paging often leads to performance issues. There can also be interesting effects when the user moves to the next page, and new rows have been added. And personally, I find paging to be quite irritating, not the least in web apps, where I have to page on, and wait for every next page.