SQL Server - Address Home/Work Radio Button on page

Hi Experts,

I have this SP:
(
@WorkFileCode nvarchar(50), 
@ReportName nvarchar(100), 
@Login nvarchar(100),
@Date1 DATE,
@ChkBox4 BIT -- Home Address Selected
@ChkBox5 BIT -- show company-paid fees
)
AS
BEGIN
SELECT        Contact, NameSort, ContactName, Email, Phone, Address, MemberExpire, CompanyPaid, EmployerID, Code1, Code2, fncPSWAllIndividualRenew.Date2, GradYear, NewCode2, 
                         HotKey, HotKeyDescription, List, fncPSWAllIndividualRenew.AddressID, Expr1, Which, IsKeyAddress, IsInvoiceAddress, Hold, DirectoryAddress, EmployerAddress, Salutation, Adressee, 
                         Position, Company, Department, Address1, Address2, City, ZipCode, State, StateDesc, County, Region, Country, CountryCode, CountryDisplay, CITYST, 
                         SortLocation, @Date1 AS Date1
FROM            dbo.fncPSWAllIndividualRenew(NULL,NULL, @WorkFileCode, @Date1,YEAR(@date1)) fncPSWAllIndividualRenew JOIN
				tmpNAsRWWorkFile ON tmpNAsRWWorkFile.ContactID = fncPSWAllIndividualRenew.Contact AND 
				tmpNAsRWWorkFile.WorkFileCode = @WorkFileCode AND
				tmpNAsRWWorkFile.ReportName = @ReportName AND
				tmpNAsRWWorkFile.Login = @Login
WHERE		@ChkBox5 = CompanyPaid AND
			Code2 <> 'WIDOW'

Open in new window


This SP currently works for "Work Address" and passes the parameter Address correctly.
But I am now adding in "Home Address" and it becomes a condition.

I need to add:
A condition on the selected "Address"
Address changes and is a parameter --Home or Work Address
depending on if user click on radio button on page
also depending on after user clicks on radio button that the record (Home Address) exists in the table

Also need to:
Left Join fncAddressHome -- where fncAddressHome = member Home Address Only.
  On tmpNAsRWWorkFile.ContactID = fncAddressHome.Contact

Currently it is working for Work Addrss Only.

The parameter that I am using for the "Radio Button" is @ChkBox4 I have already put that into the SP

Please help and thanks
Amour22015Asked:
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 HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The parameter that I am using for the "Radio Button" is @ChkBox4 I have already put that into the SP
>Currently it is working for Work Addrss Only.

Something like...
SELECT blah, blah, blah, 
   CASE @ChkBox4 
      WHEN 'Home' THEN hometable.address
      WHEN 'Work' THEN worktable.address
      ELSE '??' END as address

Open in new window

Also make sure the front-end has a default Yes or No selection on the option group / radio button, so a NULL will not be passed into SQL.
0
Amour22015Author Commented:
Hi Jim and thanks,

Isn't it more like:
SELECT blah, blah, blah, 
   CASE @ChkBox4 
      WHEN '1' THEN hometable.address
      WHEN '0' THEN worktable.address
      ELSE '??' END as address

Open in new window


But:
ELSE '??'

ELSE if Home Address does not exist then
Work Address (Even if user clicks on radio button)

Help and thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Correct, checkbox, my bad, so it would be a bit parameter with values 1 for true and 0 for false, and ignor the ELSE '??'  Can probably get away with 1 and 0 instead of '1' and '0'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Amour22015Author Commented:
Jim,

But what about:
ELSE if Home Address does not exist then
 Work Address (Even if user clicks on radio button)


What if there is no record in the:
hometable.address
THEN:
I will need the:
worktable.address???

Or does the:
SELECT blah, blah, blah, 
   CASE @ChkBox4 
      WHEN '1' THEN hometable.address
      WHEN '0' THEN worktable.address
      ELSE '??' END as address

Open in new window

already take that into consideration?

Please help and thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>But what about: ELSE if Home Address does not exist then Work Address (Even if user clicks on radio button)
Simple ISNULL will work.
SELECT blah, blah, blah, 
   CASE @ChkBox4 
      WHEN 1 THEN ISNULL(hometable.address, worktable.address) 
      WHEN 0 THEN worktable.address
      END as address

Open in new window

... or if you want to handle neither existing, use COALESCE and create some 'nothing' text...
COALESCE(hometable.address, worktable.address, 'There is no address for this person')

Open in new window

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:
Great thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.