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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply