SQL Query - conditionally create a Where clause?

Good Day Experts!

I am not sure if what I am wanting to do is even "allowed" .   So I don't spend more time looking,  hopefully you can help.  I am currently working on converting several queries to use passed in parameters in place of hard-coded values.  Some new functionality is also being added.  

I have an aliased select/sum query in the select list of a query.  I could not figure out how to use that alias to condition on in the WHERE portion of the query.  I ended up wrapping the entire Query in a Select so I could "see" and reference the aliased field in the where clause of that outer query,  I am trying to, based on an incoming parameter value, create a different Where clause.  

If the passed in parameter value is a 0,  I would need to not create the Where clause.  If the parameter value is not zero and not negative then I would want
to create  a Where clause. Since I could not figure out how to do it, I ended up conditioning on the parameter and using the query 2 times in an If/Else.  

Do you have any ideas? Is it even possible to conditionally create a Where clause?

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.

zephyr_hex (Megan)DeveloperCommented:
And now that I re-read your question... I may have misunderstood it the first time.

If you want to add a condition to the WHERE clause that's based on the result of a SUM, for example, you can wrap you original query in another SELECT, like so:

SELECT u.Total 
  SELECT SUM(MyColumn) AS Total
  FROM MyTable
) u
WHERE u.Total > 0

Open in new window

What you should consider here is that the original query (the inner one) will be performed on all records in My Table.

Another approach is to save your results to a temp table.  I use this approach if I need to do more than one operation on the inner query.  You're still performing a sum on all records, but you aren't doing it multiple times.

  SELECT SUM(MyColumn) AS Total
  INTO #tmp_table
  FROM MyTable

  FROM #tmp_table
  WHERE Total > 500

  FROM #tmp_table
  WHERE Total > 0

Open in new window


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
Scott PletcherSenior DBACommented:
WHERE (@param <= 0 OR (<normal_WHERE_conditions>))

When the @param <= 0, the other WHERE checks will be ignored (literally: SQL "short-circuits" them completely, since the WHERE clause is already known to be TRUE by matching the first condition).

Btw, do not do this:
WHERE (ISNULL(@MyParameter,MyColumn) = MyColumn
or this:
WHERE (CASE WHEN @MyParameter = NULL THEN MyColumn ELSE @MyParameter END) = MyColumn

The only proper way to code that is:
WHERE (@MyParameter IS NULL OR MyColumn = @MyParameter)

The second is "sargable", and thus index seekable, while the first (either version) is not.
HuaMin ChenProblem resolverCommented:

You can dynamically add more conditions to your SQL command.

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PortletPaulEE Topic AdvisorCommented:
it sure would help if you provided the before and after queries that you have.

>>"I have an aliased select/sum query in the select list of a query. " This sounds like a "correlated subquery" and in most recent versions of SQL Server the use of an "apply operator" is the more efficient way to re-factor these. But as always "it depends" so I really suggest that instead of us guessing what you are doing now, please show us by providing the queries.

Another technique is to supply sample data, and then an "expected result". From those 2 items we can usually generate the required query.
Jimbo99999Author Commented:
Thank you everyone for your responses. I am back at the Tues Thurs contract assignment today and will read/study all the information you provided. I appreciate your help.

Jimbo99999Author Commented:
Thank you everyone for your input.  This portion of the project was removed.  However, since I added your expert knowledge to my toolbox of solutions, I awarded points instead of removing the question.

Have a good day
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
Visual Basic.NET

From novice to tech pro — start learning today.