Professional Web Applications Themes

Specifying "do not update" values in "additive" UPDATE sprocs - Microsoft SQL / MS SQL Server

Hello, I want to write a sproc whose purpose is to perform 'additive' UPDATEs to a given table. By 'additive', I mean I would like the existing data in the table for a given record to be preserved, and to only update those fields for which I pass parameters into the sproc. So, the method I've been using is to set the sproc's parameters to default NULL, and then I test whether each parameter IS NOT NULL. If that is the case, then I do an update on the field in question, otherwise I skip over it, thereby preserving the ...

  1. #1

    Default Specifying "do not update" values in "additive" UPDATE sprocs

    Hello,

    I want to write a sproc whose purpose is to perform 'additive' UPDATEs
    to a given table. By 'additive', I mean I would like the existing
    data in the table for a given record to be preserved, and to only
    update those fields for which I pass parameters into the sproc.

    So, the method I've been using is to set the sproc's parameters to
    default NULL, and then I test whether each parameter IS NOT NULL. If
    that is the case, then I do an update on the field in question,
    otherwise I skip over it, thereby preserving the existing data for
    that record. This makes it very easy to update bits and pieces of a
    record given just the record's primary key and value(s) to be updated,
    without passing the rest of the field values in too.

    Well, the problem as you may have gathered, is that sometimes I
    actually want to update a given field to NULL -- that is, remove
    whatever value was in the field and replace it with NULL. Using the
    methodology I have described, this is a problem, since I am already
    using NULL as a signal for which fields to ignore and not update.

    Here's a sproc snippet to show you what I'm doing presently:

    ---------------------------------------------------------
    Create Procedure UpdContact
    Id int,
    FirstName varchar(50) = NULL,
    LastName varchar(50) = NULL
    As

    -- Update FirstName if specified
    If (FirstName Is Not Null)
    Update Contact
    Set FirstName = FirstName
    Where Id = Id

    -- Update LastName if specified
    If (LastName Is Not Null)
    Update Contact
    Set LastName = LastName
    Where Id = Id
    ---------------------------------------------------------


    So, the problem remains: how do I then specify that I want to update a
    certain field to become NULL?

    I'm looking for any suggestions or advice on smart ways to implement
    this cleanly. As I see it, I have a few possible ways to handle it:

    1. Set the default values of my sproc's parameters to some new "Do Not
    Update" value, which will be internally handled in the way I am
    presently handling the NULL value for skipping fields. Downside: I
    will probably need different "Do Not Update" values for each possible
    data type, and it may occur that I actually want to store the "Do Not
    Update" value itself in a field at some point (particularly likely
    with ints and booleans).

    2. Pass a special "Set To Null" value into the sproc to specify that I
    want some field(s) changed to NULL. Downsides here parallel those of
    my idea #1 above, plus I would have to rework the code which calls the
    sproc.

    3. Create a new UpdateNullValues bit parameter which will optionally
    tell the sproc whether to wipe out all existing field values and
    replace them wholesale with whatever values were passed into the
    sproc, including any NULL defaults. Downside: any time I want to
    replace a specific field's value with the NULL value, I will have to
    pass the whole set in.

    4. Create a new UpdateNullValues string parameter which is just a
    list of field-names whose values I want updated to NULL. Downside: I
    have to specifically pass a list of field names that I want set to
    NULL and therefore have to add special logic into my sproc-calling
    code.

    5. Create some kind of new special constant in SQL Server which is
    akin to the NULL value, but can be distinguished from NULL. This would
    obviously be the best solution, but the downside: as far as I know
    it's impossible.

    5. Do away with this methodology altogether. Downside: piecemeal
    updates become a much bigger hassle.


    Any advice from you T-SQL gurus would be greatly appreciated.

    Joel Thornton
    Joel Thornton Guest

  2. #2

    Default Re: Specifying "do not update" values in "additive" UPDATE sprocs

    Joel,
    > I want to write a sproc whose purpose is to perform 'additive'
    > UPDATEs to a given table. By 'additive', I mean I would like the
    > existing data in the table for a given record to be preserved,
    > and to only update those fields for which I pass parameters into
    > the sproc.
    > 2. Pass a special "Set To Null" value into the sproc to specify
    > that I want some field(s) changed to NULL. Downsides here
    > parallel those of my idea #1 above, plus I would have to rework
    > the code which calls the sproc.
    I would go with method 2. Use the lowest possible value for each
    data type to indicate null. You should also review your schema to
    see which columns really need to be nullable. In the example below I
    changed a couple of columns to allow nulls. In practice they would
    be not null.

    You should update all columns in a single statement. It is
    inefficient to do repeated updates of the same row. This fills up
    the transaction log and if you have any update triggers on this
    table, they will also be fired repeatedly.



    Create Procedure UpdContact
    Id int,
    FirstName varchar(20) = NULL,
    LastName varchar(20) = NULL,
    Title varchar(30) = NULL,
    BirthDate datetime = NULL,
    ReportsTo int = NULL
    As

    -- Update FirstName if specified
    Update Contact set
    FirstName = nullif(isnull(FirstName, FirstName), ''),
    LastName = nullif(isnull(LastName, LastName), ''),
    Title = nullif(isnull(Title, Title), ''),
    BirthDate = nullif(isnull(BirthDate, BirthDate), '1753'),
    ReportsTo = nullif(isnull(ReportsTo, ReportsTo), -32767)
    Where Id = Id

    go
    -- Make some test data
    select EmployeeID Id, LastName, FirstName, Title, BirthDate,
    ReportsTo
    into Contact
    from Northwind..Employees
    select * from Contact
    go
    alter table Contact alter column FirstName nvarchar(20) null
    alter table Contact alter column LastName nvarchar(20) null
    go
    exec UpdContact 1, FirstName = '',
    Title = 'Sales Rep',
    ReportsTo = 5,
    BirthDate = '1753'
    select * from Contact
    go
    drop table Contact
    drop procedure UpdContact



    Linda








    lindawie Guest

Similar Threads

  1. Replies: 1
    Last Post: April 24th, 01:27 PM
  2. Acro Pro Update Error: "An invalid application was selected."
    By FordRack@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 8
    Last Post: September 18th, 12:20 AM
  3. CFINPUT type="radio" w/ "value" requires "label"
    By Iceborer in forum Macromedia ColdFusion
    Replies: 2
    Last Post: February 21st, 06:16 PM
  4. why is the "Adobe Acrobat 6.0.1 Update" message there at the top? -- the update doesn't work
    By john_cummin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 6
    Last Post: August 5th, 01:51 PM
  5. "Start" "Program" "Menu" list is empty
    By Pete in forum Windows XP/2000/ME
    Replies: 2
    Last Post: July 10th, 10:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139