Update a field with a variable that can be either be a string or a NULL

MS-SQL Server 12

HTML passes a datetime string like "2018-04-25T04:00:00.000Z"  or as a NULL to PHP.

PHP creates a variable for it:
$isReady = array[ "IsReady" ];

Open in new window

If it is a datetime string then I update a table like this:
UPDATE table SET IsReady = '$isReady' WHERE RecordID = 1;

Open in new window

If it is a NULL value then I update a table like this:
UPDATE table SET IsReady = NULL WHERE RecordID = 1;

Open in new window

They both work fine but is there a way to create a single SQL UPDATE statement to handle setting the field if the variable is either a datetime string or is NULL?
Joe WeinpertSr Software EngineerAsked:
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.

gr8gonzoConsultantCommented:
Sort of. SQL Server is going to respect whatever values you send to it - it is not going to try to guess what values you want, so you need to ensure that you're sending the desired value in the query. In other words, it's your responsibility to build the query, but you can do something like this:

if($isReady != null)
{
  $query = "UPDATE table SET IsReady = '$isReady' WHERE RecordID = 1;";
}
else
{
  $query = "UPDATE table SET IsReady = NULL WHERE RecordID = 1;";
}

Open in new window


Or even more concise:
$query = "UPDATE table SET IsReady = " . ($isReady ? "'$isReady'" : "NULL") . " WHERE RecordID = 1;";

Open in new window


So you're still building one query, but you're just building it differently based on whether $isReady has a value or not.

Also, since you indicated $isReady is either blank/null or a date/time, I was able to just use a shortcut in the code with "($isReady ? ...." but if you're ever dealing with similar situations where $isReady (or whatever your variable name is in the future situation) could be the number zero (valid) or null, then you'll have to be more explicit in the condition:

$query = "start of the query blah blah SET somevar=" . ($myVar !== null ? $myVar : "NULL") . " rest of the query";

This is because PHP can treat "null", "false", and "0" all as false values in conditions, and "true" and any positive numbers or strings as true values in conditions:
<?php
$myvar = true;
if($myvar)
  echo "This will run.";
else
  echo "This will not run.";

$myvar = 1;
if($myvar)
  echo "This will run.";
else
  echo "This will not run.";

$myvar = "abc";
if($myvar)
  echo "This will run.";
else
  echo "This will not run.";

$myvar = false;
if($myvar)
  echo "This will not run.";
else
  echo "This will run.";

$myvar = 0;
if($myvar)
  echo "This will not run.";
else
  echo "This will run.";

$myvar = null;
if($myvar)
  echo "This will not run.";
else
  echo "This will run.";

$myvar = 0;
if($myvar !== null)
  echo "This will run.";
else
  echo "This will not run.";

Open in new window


So if you want to check to see if a variable is truly null, then you have to use 3 equals signs:
if($var === null)
or negate it similarly:
if($var !== null)

Or you can also use is_null, too:
if(is_null($var))

I'm just spelling this out so you're being cautious when building queries dynamically by checking the values of your variables.
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
Joe WeinpertSr Software EngineerAuthor Commented:
Yes, that is the way I am currently handling it.

I was hoping there would be a way to use the MS-SQL clauses like CASE or IF...ELSE within a single UPDATE command in a manner similar to they way they are used in the SELECT command.
0
Julian HansenCommented:
Have you had a look at ALLOW_INVALID_DATES mode?

Alternatively you could try

$query = "UPDATE table SET isReady = IF(LENGTH({$isReady})=0,NULL,{$isReady}) WHERE RecordID = 1";

Open in new window

0
gr8gonzoConsultantCommented:
I suppose in theory you could do something like this:

UPDATE table SET IsReady = (CASE WHEN ISNULL(NULLIF('$isReady', '')) THEN NULL ELSE '$isReady' END) WHERE RecordID = 1;

...but that results in a longer query, places unnecessary burden on the database, takes a little bit longer to run compared to just building the query on the client side (PHP), and makes your query less portable.

So CAN you do it? Yes. SHOULD you do it that way? Probably not, but that's your choice.

Personally, whenever I have the choice between making the web server do something versus the database server, I take every opportunity to place load on web servers instead. Web servers are extremely cheap and easily scalable and can easily handle "grunt" work like this. The DB server should be viewed as a precious, limited resource that is often used by many different applications and servers, so if you can spare it from having to do simple tasks, then you're extending the life and usability and performance of that DB server. Especially when you're dealing with a more expensive DB server like SQL server.
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
PHP

From novice to tech pro — start learning today.