Avatar of DB_Fury
DB_Fury asked on

SQL query help

I have a table that contains that contains product codes, for each product code there is a column called criteria where i have one of three values in: "NULL","Age>18","Age<18". Age is a column in a different table that will join to this table.  without using dynamic SQL or a bounch of if statements i need to figure out how i can get sql to recognize the value if its not null as part of the query....i have to use dynamic sql dont I?
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
DB_Fury

8/22/2022 - Mon
cyberkiwi

You will need dynamic sql, or some very convoluted query CASE statement for each possible input pattern.
Show more of the query and we may be able to help fill it out.
ASKER CERTIFIED SOLUTION
Ryan McCauley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
DB_Fury

thanks for yoru reply ryanmccauley, I dont quite understand the "1 = " part of the syntax...can you explain that
cyberkiwi

WHERE 1 = (CASE
             WHEN Criteria = 'Age>18' AND Age>18 THEN 1 ELSE 0
             WHEN Criteria = 'Age<18' AND Age<18 THEN 1 ELSE 0
             WHEN Criteria IS NULL THEN 1 ELSE 0
           END)

is just a fancy way to write

WHERE Criteria IS NULL
-- or do you mean the text 'NULL', which would then make it
-- WHERE Criteria = 'NULL'
OR (Criteria = 'Age>18' AND Age>18)
OR (Criteria = 'Age<18' AND Age<18)

Like I said, a [where] CASE* for each possible input pattern.

Not "case" as in the sql server case, but general English sense
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ryan McCauley

That's true - as far as results, those two options (the CASE or the multiple OR statements) accomplish the same thing. I prefer the CASE, because it makes separating out different criteria a little easier for me, but it's just personal preference.
ASKER
DB_Fury

f