Should I write a whole procedure for each database table.column I update separately?

Question

I have an application that uses AJAX liberally. I have several places where a single database column is being updated for the record the user is actively editing.

So far I've been creating separate stored procedures for each AJAX action... so I've got UPDATE_NAME, UPDATE_ADDRESS, UPDATE_PHONE stored procedures.

I was just wondering if there's a better way to continue utilizing stored procedures, but without creating one for each column.

I'd like to avoid reflecting upon a string parameter which specifies the column, if possible. I.e. I know I could have an UPDATE_COLUMN procedure which takes as one of its parameters the column name. This kind of gives me the willies, but if that's the only way to do it then I may give it some more considering. But not all columns are of the same data type, so that doesn't seem like a silver bullet.

Answer 1

Create a procedure that can update every column, but only updates columns for which you pass a non-null parameter

CREATE PROCEDURE spUpdateFoo (@fooId INT, @colA INT, @colB VARCHAR(32), @colC float)
AS

update Foo set colA = ISNULL(@colA, colA), 
    colB = ISNULL(@colB, colB), 
    colC = ISNULL(@colC, colC)
where fooId = @fooId

Note that this doesn't work if you want to be able to explicitly set null values through your procedure, but you could choose a different value to specify a non-change (-1, etc) with a little more complexity.

Answer 2

It doesn't hurt to do what you are doing, but it could get a little crazy if you continue that path. One thing you can do is create one stored procedure and assign NULL values as default parameters to all your fields that you are updating. So when you call the sproc from your app, if a parameter is given a value that value will be used in the update, otherwise the parameter will take a null value.

Then you can do a check in the sproc IF @Parameter IS NOT NULL ...

If you find yourself ever only needing to update just one field and you do not want to create one central sproc and pass nulls, then use Octavia's solution right below mine and write a simple update procedure.

Answer 3

I would say to stop using stored procedures for activities that simple, there is no justification to create so many small procedures for every single column in the database. You are much better off with dynamic sql (with parameters) for that.

Answer 4

Consider writing a single update procedure that accepts several columns and uses DEFAULT NULL for all columns that are not mandatory (as suggested by others).

Using NVL in the update will then only update the columns you provided. the only problem with this approach is, that you can't set a value to NULL.

PROCEDURE update_record (
    in_id       IN your_table.id%TYPE,
    in_name     IN your_table.name%TYPE DEFAULT NULL,
    in_address  IN your_table.address%TYPE DEFAULT NULL,
    in_phone    IN your_table.phone%TYPE DEFAULT NULL,
    in_...
) AS
BEGIN
  UPDATE your_table
  SET name = NVL( in_name, name ),
      address = NVL( in_address, address),
      phone = NVL( in_phone, phone ),
      ...
   WHERE id = in_id;
END update_record;

You can call it with named parameters then:

update_record( in_id => 123, in_address => 'New address' );

This allows you to update several columns at once when necessary.