condition in where clause with optional parameter

Question

There are thee input parameter in my stored procedure. for example,

DECLARE @fromDate DateTime = NULL DECLARE @toDate DateTime = NULL DECLARE @Id int = NULL

I want to write a condition in where clause like...if fromDate is provided then searching must be done on @fromDate. if @fromDate is not provided then check for @Id variable if this is not null then search basis on @Id...

Something like...

 where CASE when @fromDate is not null THEN (@FromDate is null or ([Created] between @FromDate and @ToDate)) ELSE (@requestId is null or Id=@requestId)

there is one problem with below solution...if @fromDate and @Id both are provided then this will do intesect of them and nothing is return.....condition should be like...if @fromDate is given the priority gives to @fromDate even if @Id is provided and result must not be dependend to @Id parameter....

Why do you want to support the ability to pass a non-null @fromDate and @Id but then ignore one of them? If I was calling your procedure and passing values for both of them, I certainly wouldn't expect it to do what you describe. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 9, 2013 at 6:27
Anyhow, the usual place to look for anything like this is Erland Sommarskog's Dynamic search conditions in T-SQL — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 9, 2013 at 6:28

revoua · Accepted Answer · 2013-04-09 06:33:33Z

Because you depend on both parameters you than can use them both in condition:

where (@FromDate is null or ([Created] between @FromDate and @ToDate)) or ((@requestId is null or Id=@requestId) and @FromDate is null) ----mix @requestId & @FromDate

TechDo · Accepted Answer · 2013-04-09 05:45:10Z

Do you mean this? Please check:

WHERE [Created] BETWEEN (CASE WHEN @FromDate IS NOT NULL THEN @FromDate ELSE [Created] END) AND (CASE WHEN @ToDate IS NOT NULL THEN @ToDate ELSE [Created] END) AND Id=(CASE WHEN @requestId IS NOT NULL THEN @requestId ELSE Id END)

Collectives™ on Stack Overflow

condition in where clause with optional parameter

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related