Case statement in Where clause

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.
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
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.
Kyle AbrahamsSenior .Net DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
@Jim, Case statement works in WHERE clause.

@Camillia, Can you explain your requirement with some sample data and expected result.
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

CamilliaAuthor Commented:
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.
CamilliaAuthor Commented:
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
pcelbaCommented:
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

CamilliaAuthor Commented:
Let me see. Thanks. I'll test and post back.
pcelbaCommented:
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.
CamilliaAuthor Commented:
pcelba's code seems to work. Yes, there's only "yes" and "no". I'll test more.
pcelbaCommented:
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

CamilliaAuthor Commented:
Yeah looks like that works. I'll test more test cases.
PortletPaulEE Topic AdvisorCommented:
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.
CamilliaAuthor Commented:
Thanks, Paul.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.