SQL Server 2008 r2 - Then Case When Then Else End

Hi Experts,

I have this part of a SP:

THEN 1 ELSE CASE fncUtlMemberDescription.Code4 WHEN @Code3 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code1 IS NULL
                      THEN CASE fncutlmemberdescription.code1 WHEN N'Prospect' THEN 0 ELSE 1 END ELSE CASE fncUtlMemberDescription.Code1 WHEN @Code1 THEN 1 ELSE 0 END END
                       = 1)

I want to also put in:
fncutlmemberdescription.code1 WHEN N'CAT1' THEN

Please help and thanks
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Post your entire code statement, as the fragment above isn't enough to answer the question.
0
Amour22015Author Commented:
Ok sure:
@Code1 nvarchar(10),
@Code2 nvarchar(10),
@Code3 nvarchar(10),
@Date1 datetime,
@Date2 datetime,
@Code4 nvarchar(10)
)
AS 

SELECT     tblNAsContacts.Contact, fncUtlContactName.Name AS ContactName, fncUtlContactName.NameSort, fncUtlKeyPhone.KeyPhoneEntry AS PrimaryPhone, 
                      fncUtlKeyFax.KeyFaxEntry AS PrimaryFax, CASE fncUtlKeyEmail.KeyEmailEntry WHEN NULL 
                      THEN fncUtlKeyEmail_1.KeyEmailEntry ELSE fncUtlKeyEmail.KeyEmailEntry END AS PrimaryEmail, fncUtlContactName_1.Name AS PrimaryName, 
                      fncUtlAddress.Salutation, fncUtlAddress.Adressee, fncUtlAddress.Position, fncUtlAddress.Company, fncUtlAddress.Department, fncUtlAddress.Address1, 
                      fncUtlAddress.Address2, fncUtlAddress.City, fncUtlAddress.State, fncUtlAddress.ZipCode, fncUtlAddress.Country, 
                      fncUtlMemberDescription.Code1 AS MemberGroupCode, fncUtlMemberDescription.MemberGroup, fncUtlMemberDescription.MemberDescription, 
                      fncUtlMemberDescription.MemberRegion, fncUtlMemberDescription.MemberChapter, dbo.fncUtlShortDate(fncUtlMemberDescription.Date1) AS JoinDate, 
                      dbo.fncUtlShortDate(fncUtlMemberDescription.Date2) AS ExpireDate, fncUtlMemberDescription.Address
FROM         tblNAsContacts INNER JOIN
                      dbo.fncUtlContactName() AS fncUtlContactName ON tblNAsContacts.Contact = fncUtlContactName.Contact INNER JOIN
                      dbo.fncUtlMemberDescription() AS fncUtlMemberDescription ON tblNAsContacts.Contact = fncUtlMemberDescription.Contact1 LEFT OUTER JOIN
                      dbo.fncNAsMemberListMainContact(@Code4) AS fncNAsMemberListMainContact ON 
                      tblNAsContacts.Contact = fncNAsMemberListMainContact.CompanyContact LEFT OUTER JOIN
                      dbo.fncUtlKeyEmail() AS fncUtlKeyEmail ON fncNAsMemberListMainContact.Contact = fncUtlKeyEmail.Contact LEFT OUTER JOIN
                      dbo.fncUtlKeyFax() AS fncUtlKeyFax ON fncNAsMemberListMainContact.Contact = fncUtlKeyFax.Contact LEFT OUTER JOIN
                      dbo.fncUtlKeyPhone() AS fncUtlKeyPhone ON fncNAsMemberListMainContact.Contact = fncUtlKeyPhone.Contact LEFT OUTER JOIN
                      dbo.fncUtlContactName() AS fncUtlContactName_1 ON fncNAsMemberListMainContact.Contact = fncUtlContactName_1.Contact LEFT OUTER JOIN
                      dbo.fncUtlAddress() AS fncUtlAddress ON fncUtlMemberDescription.Address = fncUtlAddress.AddressID LEFT OUTER JOIN
                      dbo.fncUtlKeyEmail() AS fncUtlKeyEmail_1 ON tblNAsContacts.Contact = fncUtlKeyEmail_1.Contact
WHERE     (tblNAsContacts.Status <> N'PURGE') AND (tblNAsContacts.Status <> N'DECEASED') AND (ISNULL(fncUtlContactName.ContactType, N'') <> N'Individual') AND 
                      (fncUtlMemberDescription.Date2 >= @Date1) AND (fncUtlMemberDescription.Date2 < DATEADD(dd, 1, @Date2)) AND (CASE WHEN @Code2 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code2 WHEN @Code2 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code3 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code4 WHEN @Code3 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code1 IS NULL 
                      THEN CASE fncutlmemberdescription.code1 WHEN N'Prospect' THEN 0 ELSE 1 END ELSE CASE fncUtlMemberDescription.Code1 WHEN @Code1 THEN 1 ELSE 0 END END
                       = 1)
ORDER BY fncUtlContactName.NameSort

Open in new window


Thanks
0
ste5anSenior DeveloperCommented:
Ouch. Rewrite it as e.g.

CASE WHEN @Code2 IS NULL THEN 1 
	ELSE CASE fncUtlMemberDescription.Code2 WHEN @Code2 THEN 1 
			ELSE 0 
		END 
END = 1

Open in new window


to

@Code2 IS NULL OR fncUtlMemberDescription.Code2 = @Code2

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Don't use that syntax here, it is obfuscating what you really want. E.g.
 AND (CASE WHEN @Code2 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code2 WHEN @Code2 THEN 1 ELSE 0 END END = 1)

Open in new window

can be coded shorter as
 AND (@Code2 IS NULL or fncUtlMemberDescription.Code2 = @Code2)

Open in new window

and the other conditions similar.
0
Amour22015Author Commented:
I am sorry I don't understand

I am knew to this but I am looking to add this section:

fncutlmemberdescription.code1 WHEN N'CAT1'

To:

THEN 1 ELSE CASE fncUtlMemberDescription.Code4 WHEN @Code3 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code1 IS NULL
                       THEN CASE fncutlmemberdescription.code1 WHEN N'Prospect' THEN 0 ELSE 1 END ELSE CASE fncUtlMemberDescription.Code1 WHEN @Code1 THEN 1 ELSE 0 END END
                        = 1)

 So would this not be something like:
THEN CASE (fncutlmemberdescription.code1 WHEN N'Prospect')  OR (fncutlmemberdescription.code1 WHEN N'CAT1')  THEN 0 ELSE 1 END

Only I am getting syntax errors..
What does @Code2 have to do with this?

 Please help and thanks
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Simplifying the condition of your original query is the first step to take. Then adding a condition is easy. But the way it is now is cumbersome and prone to errors.
0
Amour22015Author Commented:
Ok then so what you are saying is to first simplify this:
AND (CASE WHEN @Code2 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code2 WHEN @Code2 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code3 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code4 WHEN @Code3 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code1 IS NULL 
                      THEN CASE fncutlmemberdescription.code1 WHEN N'Prospect' THEN 0 ELSE 1 END ELSE CASE fncUtlMemberDescription.Code1 WHEN @Code1 THEN 1 ELSE 0 END END
                       = 1)
ORDER BY fncUtlContactName.NameSort

Open in new window

To:

AND (@Code2 IS NULL or fncUtlMemberDescription.Code2 = @Code2)
AND (@Code3 IS NULL or fncUtlMemberDescription.Code4 = @Code3)
AND (@Code1 IS NULL or fncutlmemberdescription.code1 WHEN N'Prospect' 
Or  fncutlmemberdescription.code1 WHEN N'CAT1' 
Or fncUtlMemberDescription.Code1 WHEN @Code1 =@Code1)
ORDER BY fncUtlContactName.NameSort

Open in new window


If any syntax please correct.

Thanks
0
ste5anSenior DeveloperCommented:
Step 1: Formatting your code to increase readability. btw, here you should use short table alias names, not simply repeating those long names.

WHERE	(tblNAsContacts.Status <> N'PURGE') AND (tblNAsContacts.Status <> N'DECEASED') 
	AND	(ISNULL(fncUtlContactName.ContactType, N'') <> N'Individual') 
	AND	(fncUtlMemberDescription.Date2 >= @Date1)
	AND (fncUtlMemberDescription.Date2 < DATEADD(dd, 1, @Date2)) 
	AND (CASE WHEN @Code2 IS NULL THEN 1 ELSE CASE fncUtlMemberDescription.Code2 WHEN @Code2 THEN 1 ELSE 0 END END = 1) 
	AND (CASE WHEN @Code3 IS NULL THEN 1 ELSE CASE fncUtlMemberDescription.Code4 WHEN @Code3 THEN 1 ELSE 0 END END = 1) 
	AND (CASE WHEN @Code1 IS NULL THEN 
				CASE fncutlmemberdescription.code1 WHEN N'Prospect' THEN 0 ELSE 1 END 
		ELSE
				CASE fncUtlMemberDescription.Code1 WHEN @Code1 THEN 1 ELSE 0 END 
		END = 1)

Open in new window


Step 2: Change it according to what we wrote. And some more.

 
WHERE	NOT tblNAsContacts.Status IN (N'PURGE', N'DECEASED')
	AND	COALESCE(fncUtlContactName.ContactType, N'') <> N'Individual'
	AND	fncUtlMemberDescription.Date2 >= @Date1
	AND fncUtlMemberDescription.Date2 < DATEADD(dd, 1, @Date2)
	AND (@Code3 IS NULL OR fncUtlMemberDescription.Code4 = @Code3)
	AND (@Code2 IS NULL OR fncUtlMemberDescription.Code2 = @Code2)
	AND (
			(@Code1 IS NULL AND fncutlmemberdescription.code1 <> N'Prospect') 
			OR 
			(NOT @Code1 IS NULL AND fncutlmemberdescription.code1 = @Code1)
		)

Open in new window


Now, tell us how your new condition fits into this.
0

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
Amour22015Author Commented:
So I have this:
AND (@Code2 IS NULL or fncUtlMemberDescription.Code2 = @Code2)
					  AND (@Code3 IS NULL or fncUtlMemberDescription.Code4 = @Code3)
					  AND (@Code1 IS NULL or fncutlmemberdescription.code1 = N'Prospect' 
					  Or  fncutlmemberdescription.code1 = N'CAT1' 
					  Or fncUtlMemberDescription.Code1 = @Code1)

Open in new window


Is that the same? as:
AND (CASE WHEN @Code2 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code2 WHEN @Code2 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code3 IS NULL 
                      THEN 1 ELSE CASE fncUtlMemberDescription.Code4 WHEN @Code3 THEN 1 ELSE 0 END END = 1) AND (CASE WHEN @Code1 IS NULL 
                      THEN CASE fncutlmemberdescription.code1 WHEN N'Prospect' THEN 0 ELSE 1 END ELSE CASE fncUtlMemberDescription.Code1 WHEN @Code1 THEN 1 ELSE 0 END END
                       = 1)

Open in new window


Thanks for helping.
0
Amour22015Author Commented:
So then it would be like:
WHERE	NOT tblNAsContacts.Status IN (N'PURGE', N'DECEASED')
						AND	COALESCE(fncUtlContactName.ContactType, N'') <> N'Individual'
						AND	fncUtlMemberDescription.Date2 >= @Date1
						AND fncUtlMemberDescription.Date2 < DATEADD(dd, 1, @Date2)
						AND (@Code3 IS NULL OR fncUtlMemberDescription.Code4 = @Code3)
						AND (@Code2 IS NULL OR fncUtlMemberDescription.Code2 = @Code2)
						AND (
								(@Code1 IS NULL AND fncutlmemberdescription.code1 <> N'Prospect') 
								OR
								(@Code1 IS NULL AND fncutlmemberdescription.code1 <> N'CAT1')
								OR 
								(NOT @Code1 IS NULL AND fncutlmemberdescription.code1 = @Code1)
							)

Open in new window


Thanks
0
ste5anSenior DeveloperCommented:
Shorter:

(@Code1 IS NULL AND NOT fncutlmemberdescription.code1 IN ( N'Prospect',  N'CAT1'))

Open in new window

0
Amour22015Author Commented:
Thanks for helping me all the way.  Great Job!
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.