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

[Joel Thornton]

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

[lindawie]

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