Case statement in Where clause

Camillia
Camillia used Ask the Experts™
on
This is SQL 2014.

1. I have this where clause that works

DECLARE @ManagedOrNon VARCHAR(100) = N'N/A - Non-Managed'

where
...
AND 
      (
          s.LesseeCode IS NULL
          OR (NOT (
                      s.LesseeCode = @ManagedOrNon
                      AND (ISNULL(s.LesseeCode, 1) = ISNULL(@ManagedOrNon, 1))
                  )
             )
      )

Open in new window


2. I want to duplicate what the C# code has and add and Else statement

if (nonManaged == NonManaged.No)
		q = q.Where(x => x.Schedule.LesseeCode == null || x.Schedule.LesseeCode != Constants.NonManagedAssetScheduleCode);
	else
	if (nonManaged == NonManaged.Yes)
	q = q.Where(x => x.Schedule.LesseeCode == Constants.NonManagedAssetScheduleCode);

Open in new window


3. I think I can use a CASE statement but this didn't work

Where
   ....

 s.LesseeCode = CASE WHEN @ManagedOrNon = N'N/A - Non-Managed'
	                      THEN
                              s.LesseeCode IS NULL
					  OR 
					 (NOT (
						  s.LesseeCode = @ManagedOrNon
							  AND (ISNULL(s.LesseeCode, 1) = ISNULL(@ManagedOrNon, 1))
							  )
						 )
						  ELSE
                                                       s.LesseeCode = 'Managed'
						  END 

Open in new window


I'll work on it. If I figure it out, I'll post back.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
In SQL Server T-SQL CASE statements do not work in WHERE clauses, I don't remember why off the top of my head, so you're better off using the boolean AND..OR logic in your first set.
Senior .Net Developer
Commented:
you have to do the compare and return a 1 / 0:

Also just a note you shouldn't hard code strings.

Where
   ....

1 = 
 --return 1 when you want to include the record.
          CASE WHEN @ManagedOrNon = N'N/A - Non-Managed'
	                      THEN
                                   isnull(s.LesseeCode,1)  -- return 1 if lessecode is null
		when 
						  isnull(s.LesseeCode,1) = isnull(@ManagedOrNon,1)
                                    then 1
		when  s.LesseeCode = 'Managed' and @ManagedOrNon <> N'N/A - Non-Managed'
                                   then 1 
                else 0 -- otherwise don't include the record.
		end 

Open in new window

SharathData Engineer
Commented:
@Jim, Case statement works in WHERE clause.

@Camillia, Can you explain your requirement with some sample data and expected result.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Let me try, Kyle. The hardcoded string is for testing. I'm going to pass it to the stored proc from code.

I'll post back.
What line corresponds to what?

1.
  CASE WHEN @ManagedOrNon = N'N/A - Non-Managed'
	                      THEN
                                   isnull(s.LesseeCode,1)

Open in new window


is the same as s.LesseeCode IS NULL

2.  Now I have the "Or Not" part

 OR 
	  (NOT (
			s.LesseeCode = @ManagedOrNon
			 AND (ISNULL(s.LesseeCode, 1) = ISNULL(@ManagedOrNon, 1))
			 )
	 )

Open in new window


a. Should this be because I have "Not"?
when
        isnull(s.LesseeCode,1) != isnull(@ManagedOrNon,1)
              then 1
b. And
when  s.LesseeCode =  @ManagedOrNon <> N'N/A - Non-Managed'
                                   then 1
If you would like to have the best fit between SQL code and C# code then you will need to define two variables which will replace the C# constants in SQL code:
DECLARE @NonManagedAssetScheduleCode nvarchar(20) = N'N/A - Non-Managed'
DECLARE @nonManaged varchar(3) = 'Yes'

WHERE
...
  AND (
      CASE WHEN @nonManaged = 'No' AND (s.LesseeCode IS NULL OR s.LesseeCode != @NonManagedAssetScheduleCode) THEN 1 ELSE 0 END = 1
   OR CASE WHEN @nonManaged = 'Yes' AND s.LesseeCode = @NonManagedAssetScheduleCode THEN 1 ELSE 0 END = 1
   )

Open in new window

Let me see. Thanks. I'll test and post back.
Note: The above code expects just two possible values in @nonManaged variable ('Yes' or 'No'). If for any occasion the third value appears then the C# code returns all rows but my SQL equivalent returns no rows.

Please let me know if this is OK.
pcelba's code seems to work. Yes, there's only "yes" and "no". I'll test more.
In fact you don't need CASE structure...
WHERE
...
  AND (
      (@nonManaged = 'No' AND (s.LesseeCode IS NULL OR s.LesseeCode != @NonManagedAssetScheduleCode))
   OR (@nonManaged = 'Yes' AND s.LesseeCode = @NonManagedAssetScheduleCode)
   )

Open in new window

Yeah looks like that works. I'll test more test cases.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Just a tiny point - no need to alter any code or do more testing. Apologies in advance.

nb: I am pedantic - or so my children tell me.

The correct term is "case expression"

(An "expression" evaluates to a single value) and that is exactly what a case expression does.

(the term "statement" refers to bigger constructs such as a "select statement" and "update statement")

Not that is affects SQL Server, but in some databases a "case statement" is a type of flow control  that in TSQL would be handled using IF/ELSE.

Happy New Year.
Thanks, Paul.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial