March 12, 2020 at 4:31 pm
blank is not NULL,it is emptystring - you need to use DBNULL
if you are passing empty strings in then how does sql know if you want to set the value to empty or ignore it...
which means you have to go to for
case when @firstname='' then firstname else @firstname end
as in my first post
MVDBA
March 12, 2020 at 4:37 pm
Yes you are right and I thought you should be this totally works but mint does not.
And I agree totlay this should work, looks like what I am doing too?
But thanks for your help, I guess I will just have to play with it.
Thank you
March 12, 2020 at 5:10 pm
Yes you are right and I thought you should be this totally works but mint does not.
And I agree totlay this should work, looks like what I am doing too?
But thanks for your help, I guess I will just have to play with it.
Thank you
Please take the time to understand the difference between NULL (the absence of any data) and '' (empty string). This is a key concept.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 12, 2020 at 6:41 pm
If the parameter can be blank or null - you can modify the update query to this:
UPDATE t
SET FirstName = coalesce(nullif(@first_name, ''), FirstName),
LastName = coalesce(nullif(@last_name, ''), LastName), etc etc
FROM table1 t
WHERE SomeId = @some_id
You can use coalesce or isnull - I prefer coalesce. The nullif will return NULL if the parameter is a blank or null value - defaulting the value to be updated to the actual column value.
Another method would be to validate the parameter(s) and set them accordingly:
SET @first_name = nullif(@first_name, '');
SET @last_name = nullif(@last_name, '');
And then the isnull will work as expected.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply