Ignoring parameters that haven't been specified in a SQL query
I answered a question on Stackoverflow today that was looking to cater for having parameters to the query that aren't always required, i.e.
The report has three parameters, Sales Id, Start Date and End Date. The latter two are used as a range (so as a pair) and the former as a direct match
In this case there's a fairly simple trick you can use to allow for this, the ISNULL or COALESCE function.
Say you've got just one parameter, Sales Id, and if it's not specified then you want all records to be returned - a query like this will do the trick:
DECLARE @SalesId INT = NULL
SELECT *
FROM [dbo].[SalesDb]
WHERE SalesId = ISNULL(@SalesId, SalesId)
The WHERE clause is quite simply saying give me all the records where the SalesId is equal to @SalesId, if it's not null, or itself if @SalesId is null, which caters for both scenarios quite nicely.
It gets a little bit more complicated when there are multiple parameters introduced (the example from the original question) but it's still just a matter of adding more to the WHERE clause:
DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesId INT = NULL
SELECT *
FROM [dbo].[SalesDb]
WHERE SalesId = ISNULL(@SalesId, SalesId) AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
Of course there's no inherent logic in the SQL query to ensure that @Start is before @End or that both @Start and @End have ben specified but in the case of a stored procedure you'd want to drop some logic into the top to validate those rules and react accordingly.
The queries above are performed using the following schema and sample data:
CREATE TABLE SalesDb
(
SalesId INT NOT NULL,
SalesDate DATETIME NOT NULL,
SaleValue DECIMAL(10, 2),
)
GO
INSERT
INTO SalesDb
(
SalesId, SalesDate, SaleValue
)
VALUES (15, '2019-01-01', 12),
(16, '2019-01-02', 34),
(16, '2019-01-03', 56),
(16, '2019-01-04', 78)