CODEWITHSUNDEEP
sql-server
Philip
Philip

Reputation: 2628

Utilising optional parameters in where clause

I have the following sample query within a stored procedure where the @StartDate, @EndDate and @ClientID parameters are all optional.

What is the best way to handle that within the query to ensure I get a result depending on whether 1 or many parameters have values?

select * from table
WHERE
    StartDate >= @StartDate and
    StartDate <= @EndDate and
    CE.ClientID = @ClientID

For Example, someone could just enter a Start Date or just enter an End Date or select a particular ClientID or do a combination of all 3.

Upvotes: 1

Views: 1651

Answers (3)

dean
dean

Reputation: 10098

If you're willing to sacrifice a tiny amount of time on each execution, OPTION(RECOMPILE) will provide the performance equal to dynamic SQL but without all the perils of it.

select * from table
WHERE
    (StartDate >= @StartDate or @StartDate is null) and
    (StartDate <= @EndDate or @EndDate is null) and
    (CE.ClientID = @ClientID or @ClientID is null)
option(recompile)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The best way is to use dynamic SQL. Something like this:

declare @sql nvarchar(max);
set @sql = 'select * from table';

declare @where nvarchar(max);

set @where = (case when @StartDate is not null then ' and StartDate >= @StartDate' else '' end) +
             (case when @EndDate is not null then ' and EndDate >= @EndDate' else '' end) +
             (case when @ClientID is not null then ' and ClientID = @ClientID' else '' end);

set @where = stuff(@where, 1, 5, '');

set @sql = @sql + (case when len(@where) > 0 then ' where ' + @where' else '');

exec sp_executesql @sql,
                   N'@StartDate date, @EndDate date, @ClientId int',
                   @StartDate = @StartDate, @EndDate = @EndDate, @ClientId = ClientId;

The reason this is better is because each possible combination of inputs results in a different query. SQL Server can optimize the queries using appropriate indexes, and such optimization can be important when using optional parameters.

Upvotes: 1

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

You can do something like this - 

SELECT * FROM table
WHERE
    (@StartDate IS NULL OR StartDate >= @StartDate) AND
    (@EndDate IS NULL OR StartDate <= @EndDate) AND
    (@ClientID IS NULL OR CE.ClientID = @ClientID)

Upvotes: 1

Related Questions