SQL Conditional Where Clause Not Use Subquery?

Posted on 2014-07-14
Medium Priority
Last Modified: 2014-07-14
I have a typical Stored Procedure using a Where clause and Max(Update_Date) subquery. I need to conditional NOT use the Max(Update_Date) query, completely by-pass it.  I haven't written a query like this before and unsure how to proceed, help...

1. If @myVariableString is empty, then use the Max(Update_Date) query
2. If @myVariableString not empty, then by-pass the Max(Update_Date) query all together.

@myVariableString varchar(4000)

Select   * 
From    Advertising a
Where Case When @myVariableString = '' 
                      Then  Update_Date = (Select Max(Update_Date) 
                                                      From Advertising b 
                                                      Where a.Name = b.Name)

Open in new window

Question by:WorknHardr
LVL 21

Expert Comment

by:Randy Poole
ID: 40195105
Not 100% what your looking for, but see if this is it:

Select   *,case when isNull(@myVariableString,'')='' then 
(Select top 1 Update_Date From Advertising b Where a.Name = b.Name order by Update_Date desc) 
else Update_Date end as Update_Datenew
From    Advertising a

Open in new window

LVL 70

Accepted Solution

Scott Pletcher earned 1800 total points
ID: 40195133
Select   *
From    Advertising a
    (@myVariableString > '' OR a.Update_Date = (Select Max(b.Update_Date)
                                                      From Advertising b
                                                      Where a.Name = b.Name))

Author Closing Comment

ID: 40195255
Excellent! thx

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question