How to update a Tables with randum Null parameters

  • itmasterw 60042

    SSCrazy

    Points: 2780

    Hi,

    I have a number of parameters (see below ) that come into a Stored procedure. In the application that this is called from the user may have only some of these (or maybe just one of these) being updated at a given call. Now I could do this with a series of IF statements Like if first name is not NULL set first name and update. But that seems a bad way to do it is there a better way, one in which I can do it in one step.

    My parameters ccomnming in:

    @first_name [varchar](50),

    @last_name [varchar](50),

    @add1 [varchar](50),

    @add2 [varchar](50),

    @city [varchar](25),

    @state [varchar](2),

    @zip [varchar](9),

    Thank you

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    just a thought (might be a bad one)

    build a table variable or temp table and put the values in there in a format that mimics the target table (as a single row)

    then use a merge or update statement

    update target set firstname=case when source.firstname is null then target.firstname else source.firstname,

    lasttname=case when source.lastname is null then target.lastname else source.lastname, ......

    from xxx as source inner join yyy as target on …...

    you get the picture?

     

     

    MVDBA

  • Phil Parkin

    SSC Guru

    Points: 244449

    Building the update query using dynamic SQL is one way. Alternatively, something like this should work:

    UPDATE t
    SET FirstName = IsNull(@first_name, FirstName),
    LastName = IsNull(@last_name, LastName), etc etc
    FROM table1 t
    WHERE SomeId = @some_id

    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.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    or even better, rather than building up the table variable inside the proc , could you pass a TVP through?

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    phil's idea is better than mine

    MVDBA

  • itmasterw 60042

    SSCrazy

    Points: 2780

    Thanks that looks far better than what I was thinking.

    Thank you

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    I know this might sound stupid, but check to see if all of your parameters are NULL - then don't update a row that is setting itself to itself.

     

    MVDBA

  • itmasterw 60042

    SSCrazy

    Points: 2780

    The problem I ma haveing with your solution is that while it updates waht I want it sets all the other fields to noull wiping out what they had.

    I need them to stay and the ones I changed only to change.

    any ideas?

  • itmasterw 60042

    SSCrazy

    Points: 2780

    I do not see how you would do this. would you have an example?

    Thank you

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    itmasterw 60042 wrote:

    I do not see how you would do this. would you have an example?

    Thank you

    nope - both phil and my solution set the value to itself if the parameter is null - only the fields where the parameter is not null will be changed

    from phil's example

    SET FirstName = IsNull(@first_name, FirstName),

    if you set @firstname to null then it keeps it's value (isnull(@firstname,firstname) will evaluate to the existing value)

    MVDBA

  • itmasterw 60042

    SSCrazy

    Points: 2780

    Well this is what I have below, and if the parameter is NULL it is wiping it out.

    Am I doing it wrong somehow?

    UPDATE [dbo].[DebtorACH]
    SET first_name = IsNull(@first_name, first_name),
    ...others here

    FROM [dbo].[Debtor]
    WHERE file_no = @file_no

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    itmasterw 60042 wrote:

    Well this is what I have below, and if the parameter is NULL it is wiping it out.

    Am I doing it wrong somehow?

    UPDATE [dbo].[DebtorACH]
    SET first_name = IsNull(@first_name, first_name),
    ...others here

    FROM [dbo].[Debtor]
    WHERE file_no = @file_no

    are you passing in NULL or 'NULL' ?

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    give me 2 minutes and i'll get you a working example

    MVDBA

  • itmasterw 60042

    SSCrazy

    Points: 2780

    Okay, I am working in a VB application and am just, for now, I am just changing the first name. So the other fields would be left blank which if I am not mistaken would be a NULL, but maybe I am wrong?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    ok - here is an example that does not null out your columns

    CREATE TABLE x (id int, firstname VARCHAR(50),lastname VARCHAR(50))
    GO
    INSERT INTO x SELECT 1,'mike','jones'
    go

    CREATE PROC upd @id int,@firstname varchar(50),@lastname varchar(50)
    AS
    UPDATE x SET firstname=ISNULL(@firstname,firstname),lastname=isnull(@lastname,lastname)
    GO
    SELECT * FROM x
    GO
    EXEC upd 1, NULL,NULL
    go
    SELECT * FROM x

    i put a select statement at the end to show you that calling the proc with NULL values does not change the data

     

    MVDBA

Viewing 15 posts - 1 through 15 (of 19 total)

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