January 14, 2022 at 4:50 pm
My WHERE clause is conditional on a parameter such that I need to do as follows:
--If the parameter @tiDepth > 0 then the WHERE Clause is:
WHERE @MDepth > @tiDepth
-- If the parameter @tiDepth = 0 then the WHERE Clause should be:
WHERE @MDepth >= @tiDepth
Ideally I was hoping to do this:
WHERE @MDepth
CASE @tiDepth
WHEN 0 THEN >=
ELSE >
END;
@tiDepth
BUT I GET AN ERROR IN T-SQL
The question is how to use CASE structure in T-SQL to choose a conditional LOGIC operator in an SQL statement WHERE clause.
January 14, 2022 at 5:03 pm
CASE is an expression not a statement. That means that the result of WHEN or THEN must be a single value. No operators (>,>=, etc.) and no keywords can be in the WHEN or THEN expression.
Here you don't really need a case, you can just check the specific conditions in the WHERE clause:
WHERE ((@tiDepth = 0 AND @MDepth >= 0) OR (@tiDepth > 0 AND @MDepth > @tiDepth))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 14, 2022 at 7:06 pm
Instead of using a CASE expression (as you were trying) - or stacked OR's:
WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)
Consider: @mDepth >= 0 is equivalent to @mDepth > -1
Of course, I am not sure why you would be checking 2 variables in the WHERE clause anyways. If you really are checking 2 variables - that should be done outside the query. Or - just reset @tDepth before you run the query:
SET @tDepth = iif(@tDepth = 0, -1, @tDepth);
Then:
IF @mDepth > @tDepth
BEGIN
{your query here - without the WHERE}
END
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 14, 2022 at 8:12 pm
Instead of using a CASE expression (as you were trying) - or stacked OR's: WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)
Consider: @mDepth >= 0 is equivalent to @mDepth > -1
Of course, I am not sure why you would be checking 2 variables in the WHERE clause anyways. If you really are checking 2 variables - that should be done outside the query. Or - just reset @tDepth before you run the query: SET @tDepth = iif(@tDepth = 0, -1, @tDepth);
Then:
IF @mDepth > @tDepth
BEGIN
{your query here - without the WHERE}
END
I hate those types of unnecessary tricks, "tricks for the sake of tricks". They're very hard for someone else to follow later, like say 4 months from now. There's no real gain from that vs just doing the straightforward comparisons needed.
I mean, c'mon, it was confusing enough that you felt the need to comment on it in yourself. Why add confusion for no real reason?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 15, 2022 at 4:46 pm
Jeffrey Williams wrote:Instead of using a CASE expression (as you were trying) - or stacked OR's: WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)
Consider: @mDepth >= 0 is equivalent to @mDepth > -1
Of course, I am not sure why you would be checking 2 variables in the WHERE clause anyways. If you really are checking 2 variables - that should be done outside the query. Or - just reset @tDepth before you run the query: SET @tDepth = iif(@tDepth = 0, -1, @tDepth);
Then:
IF @mDepth > @tDepth
BEGIN
{your query here - without the WHERE}
ENDI hate those types of unnecessary tricks, "tricks for the sake of tricks". They're very hard for someone else to follow later, like say 4 months from now. There's no real gain from that vs just doing the straightforward comparisons needed.
I mean, c'mon, it was confusing enough that you felt the need to comment on it in yourself. Why add confusion for no real reason?
Instead of going vertical, why not ask if there's a reason? I'll get the popcorn. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply