FindInvoices Stored Procedure

CREATE PROCEDURE FindInvoices
    @InvoiceNumber INT = NULL,
    @InvoiceDate DATE = NULL,
    @AccountNumber INT = NULL,
    @ProductNumber INT = NULL,
    @Quantity INT = NULL
AS
BEGIN
    SELECT *
    FROM Invoices
    WHERE (@InvoiceNumber IS NULL OR InvoiceNumber = @InvoiceNumber)
        AND (@InvoiceDate IS NULL OR InvoiceDate = @InvoiceDate)
        AND (@AccountNumber IS NULL OR AccountNumber = @AccountNumber)
        AND (@ProductNumber IS NULL OR ProductNumber = @ProductNumber)
        AND (@Quantity IS NULL OR Quantity = @Quantity)
END

This code creates a stored procedure named FindInvoices that takes five parameters: @InvoiceNumber, @InvoiceDate, @AccountNumber, @ProductNumber, and @Quantity.

The procedure selects all records from the Invoices table where the invoice number matches the given @InvoiceNumber parameter, the invoice date matches the given @InvoiceDate parameter, the account number matches the given @AccountNumber parameter, the product number matches the given @ProductNumber parameter, and the quantity matches the given @Quantity parameter.

Using this stored procedure, you can locate invoices based on any combination of the provided parameters. If a parameter is not provided or is set to NULL, it will be ignored in the filtering process.

Example usage:

EXEC FindInvoices @ProductNumber = 1234, @Quantity = 10

This example will return all invoices where the product number is 1234 and the quantity is 10.