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?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
 
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 
FROM (
  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

 SELECT Total
  FROM #tmp_table
  WHERE Total > 500

SELECT Total
  FROM #tmp_table
  WHERE Total > 0

Open in new window

0
 
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.
1
 
HuaMin ChenSystem AnalystCommented:
Hi,

You can dynamically add more conditions to your SQL command.
Read

https://stackoverflow.com/questions/16801988/building-a-dynamic-sql-query-from-vb-net
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
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.
0
 
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.

Thanks,
jimbo99999
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.