replace values in update statement

I have an update statement that looks like this:

update facility set country_code = ? where facility_id = 1

this questionmark is evaluated by Java based on the user entered values.  Since Country_code is a string, if the user does not enter any values, java tries to put in  '' (2 single quotes)....however country_code is a foreign key so the statement will blow up...

What I need is a way to evaluate what is being put in place of the question mark....if it is anything other than '', then I want to leave it alone, if it is '', then I want to insert a NULL.

Does that makes sense?
jl1884Asked:
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.

LazyStudentCommented:
if country_code is foreign key - you have to update with values that already exist in other table ( the one you made foreign key with - let's call it FOREGN_TABLE).
If you want to allow anyway NULL values in your facility table - you can define in table FOREGN_TABLE value NULL - and it will be default value with no input.

I hope I understood you and answered the question.
0
nigelrivettCommented:
update facility
set country_code = case when @s = '' then null else @s end
where facility_id = 1
0
johan_brohnCommented:
/*If country doesnt exist, do not update*/
UPDATE F
SET Country_Code = C.Country_Code
FROM Facility F
JOIN Country C On C.Country_Code = ?
WHERE F.Facility_Id = 1

/*If no rows affected return an error*/
IF @@RowCount = 0
  RaisError ('mr Java developer, please add som input validation!', 16, 1)
0
KatanaCSCommented:
If the update statement is being assigned by the Java code, then it'll need to be evaluated in Java (as suggested by johan_brohn's RaisError).  

If the update statement is being assigned in SQL (i.e. through a stored procedure call), then you can test for validation in the procedure.  If you validate all of the parameters in SQL, you'll most likely have to use dynamic SQL to perform the actual update.
   If IsNull(@Country, '') <> '' -- Include in update statement
         Select @SQL = @SQL + ', country = ''' + @Country + ''''


Regards,
KatanaCS
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
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.

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.