Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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?
1 Solution
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.
update facility
set country_code = case when @s = '' then null else @s end
where facility_id = 1
/*If country doesnt exist, do not update*/
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)
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 + ''''


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now