How to update a Tables with randum Null parameters

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

  • itmasterw 60042

    SSCrazy

    Points: 2780

    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

  • Phil Parkin

    SSC Guru

    Points: 244449

    itmasterw 60042 wrote:

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeffrey Williams

    SSC Guru

    Points: 88460

    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 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply