Optional sql query where param

I have an optional parameter in my sql query.   How do i code the "where" statement in the query to (1) when the optional parameter is filled in to use it,  and when its blank or null pull "everything"

where student = parm1 else student = <pull all students>
Mike JohnsonBusiness Inteligence DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
where ( student = parm1 or param1 is null )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
where ( student = parm1 or param1 is null or param1='' )
where student = decode (student,parm1,parm1, student)

try this
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

awking00Information Technology SpecialistCommented:
Is this part of a procedure? If so, can you post it?
There's always

WHERE student LIKE parm1

Assuming you set the parm1 default to the wildcard for your db, any valid input overwrites the wildcard.  Otherwise, you get everything.


awking00Information Technology SpecialistCommented:
where student like nvl(param1, student)
It seems this question is not concerned to SQL.
You just need to provide user-friendly query builder in your program. If user add some condition(s) - that builder will create necessary SQL statement if not - you can use some "default" statement (SELECT * FROM Sometable).
awking00 had the better solution, in my professional opinion.  it stands alone, it's extremely simple and compact, and requires no OR logic.  So, I object to the points split.  I really think awking00 has it.
awking00Information Technology SpecialistCommented:
Thanks for your support. The only problem I see is that this is an Oracle solution and an awful lot of questioners use this zone (annoyingly so) when they're working with another RDBMS. Even so, I'm sure there is corresponding syntax (e.g. IFNULL) in other databases that could be applied. The points aren't that important to me. I just want to see questioner's problems resolved and wish that they would acknowledge the same.
Given the context of the forum, you provided an optimal Oracle solution.  I understand the greater context of the problem, but "SQL syntax" is the appropriate vendor-neutral forum.  I just wanted to stipulate it for the record, in the event someone looks at the thread and actually wants the Oracle solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.