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:


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

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:

    SalesId INT NOT NULL,
    SaleValue DECIMAL(10, 2),

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)

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment