views:

138

answers:

4

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.

A: 

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.

sidereal
Not Oracle but I get the message. Crediting this answer since it was the first with this suggestion.
RenderIn
A: 

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.

JonH
+1  A: 

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.

Otávio Décio
You never know what changes might be made later on though, if today you need to change one value, tomorrow may be a different story. Still not a bad idea though. Depends on the situation.
JonH
@JonH - man, this is really radical - creating one sp per column! It makes me cringe but oh well, I know there are times when sp's are the only allowed path to the db.
Otávio Décio
@Otavio - I did not say that...read the post otavio, my suggestion was to create one stored procedure and use a NULL parameter for all fields that do not need an update.
JonH
@JonH - I was referring to the OP's question, sorry. I see, yes - that is a good alternative if you are mandated to use sp's and I've seen it done before. BTW people can downvote me until hell freezes over but I still think it is overkill.
Otávio Décio
@Otavio - now that was funny. I think your method is just as elegant :). +1.
JonH
Dynamic SQL should usually be reserved for those cases where it is just impossible or very impractical to use static SQL. It is much easier to introduce difficult-to-trace bugs because it sidesteps a lot of compile-time checks, and unless done carefully can make a mess of the SQL cursor cache. I wouldn't normally recommend dynamic SQL over the alternative.
Jeffrey Kemp
@Jeffrey - how hard or dangerous is it to create a one liner dynamic parameterized sql to update a *single* column, that in the end gets parsed and cached as any other sp? My personal preference is to use an ORM but didn't want to complicate things.
Otávio Décio
@Otavio - not hard if the developer knows what they're doing - my experience is that I've fixed many more problems (defects and performance issues) due to poor use of Dynamic SQL than anything else. They're just so much easier to abuse. Anyway, if it's a one-liner, how hard is it to make turn it into static SQL?
Jeffrey Kemp
@Jeffrey - the issue (at least for me) is polluting the stored procedures with small one-line sp's unnecessarily.
Otávio Décio
@Otavio - agreed, a one-liner for each and every column is overkill.
Jeffrey Kemp
+3  A: 

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.

Peter Lang
@Peter Lang - this was already mentioned 10 minutes ago.
JonH
@JonH: That's right (edited my answer to mention that) - but I was really missing some PL/SQL example...
Peter Lang
That won't work if you want to update a column from "a value" to NULL.
Paul James
@Paul James: Now that's why I wrote `the only problem with this approach is, that you can't set a value to NULL.` :)
Peter Lang
One way to get around the "can't set to NULL" problem is for the client to always retrieve the entire record, and pass all the values to update_record; and remove the `DEFAULT NULL`s and `NVL`s.
Jeffrey Kemp
@Peter Lang - oh yea, serves me right for not reading the spec properly :)
Paul James