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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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