Conditional WHERE clause with operator in statement [duplicate]

Question

I have a stored procedure that I'm trying to do a conditional WHERE clause on. I believe the solution is easy but for some reason it's escaping me.

What I'm trying to achieve is: if a ID (@pbid in this case) is passed to the stored procedure I only want to return a single record matching that parameter otherwise I'd like to return all results in the table.

Here is the what I have come up with and it's obviously not going to work because I'm trying to do an equals evaluation at PBID (PBID =) and I'm looking to conditionally make that

 where pb.PBID =
    CASE WHEN @pbid is not null THEN 
    @pbid  --just return results with this ID
    ELSE
    is not null --return all results
  END

If it was C# or something I'd write it like:

if(intPBID > 0) --parameter set, only return results with that param
{
   pb.PBID = intPBID
}
else
{
  pb.PBID > 0 --return everything
}

I hope this isn't too confusing and I'd appreciate any feedback.

Thanks

Matthew Haugen · Accepted Answer · 2018-07-18 19:49:56Z

2

I'm not sure about performance implications, but I typically end up doing something like:

SELECT *
FROM table
WHERE (@pbid IS NULL OR pb.PBID = @pbid)

answered Jul 18, 2018 at 19:49

Matthew Haugen

13.4k5 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jon.r Over a year ago

I knew it was insanely simple. That worked beautifully. I was overthinking it I suspect!

S3S Over a year ago

Here's the performance piece: blogs.sentryone.com/aaronbertrand/…

jon.r Over a year ago

@scsimon I realize this could potentially be a problem but it's being using for a very small data set. Thank you for the info though.

S3S Over a year ago

No worries at all @Waragi I use the same method Matt posted frequently. I was just providing him a link about the performance since he mentioned it.

Collectives™ on Stack Overflow

Conditional WHERE clause with operator in statement [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related