Do I have to write a whole procedure for each database table. Column do I update separately?

Question

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

So far, I have created separate stored procedures for each AJAX action ... so I have stored procedures UPDATE_NAME, UPDATE_ADDRESS, UPDATE_PHONE.

I'm just wondering if there is a better way to continue using stored procedures, but not creating them for each column.

I would like to avoid reflecting the row parameter that indicates the columns, if possible. That is, I know that I can have an UPDATE_COLUMN procedure that takes a column name as one of its parameters. This view gives me freedom, but if this is the only way to do this, I can give him a few more thoughts. But not all columns have the same data type, so this does not look like a silver bullet.

Answer 1

Create a procedure that can update each column, but only updates the columns for which you pass a nonzero 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

, , , , (-1 ..) .

Answer 2

, DEFAULT NULL , ( ). ​​

NVL , , . , 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;

, :

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

, .

Answer 3

, , . sql ( ) .

Answer 4

, , , . , , NULL , . , sproc , , , .

sproc IF @Parameter IS NOT NULL ...

, sproc null, Octavia .