UPDATE stored procedure with conditional arguments-CodePudding

I want to write an Oracle UPDATE stored procedure where I reset certain fields if BIT argument is true. If BIT argument is not true, then update should take the already existing value or skip updating the field. How can I achieve this?

What I am trying to achieve more or less is this

CREATE OR REPLACE PROCEDURE [dbo].[p_update_fields]
    @ID                  varchar(255),
    @RESET_FIELD_1    boolean,
    @RESET_FIELD_2    boolean,
    @RESET_FIELD_3    boolean
    
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION
        IF EXISTS (SELECT id FROM dbo.TABLE_NAME WHERE id = @ID)
            declare field1_;
            declare field2_;
            select t.field1, t.field2 INTO field1_, field2_ FROM dbo.TABLE_NAME t where t.id = @ID
            if(@RESET_FIELD_1 = true) 
               field1_ = null;
            if(@RESET_FIELD_2 = true)
               field2_ = null;
            UPDATE dbo.TABLE_NAME
                SET
                    field1 = field1_,
                    field2 = field2_
            WHERE id = @ID
    END TRANSACTION
    SET NOCOUNT OFF;
END

CodePudding user response：

If you do use Oracle, then have a look at the following example. I chose not to pass Boolean but numeric values (they are simpler to use; 1 represents "true", 0 is then "false").

Sample data:

SQL> SELECT * FROM table_name ORDER BY id;

ID       FIELD1     FIELD2     FIELD3
---- ---------- ---------- ----------
A123        100        200        300
B456          1          2          3

Procedure:

SQL> CREATE OR REPLACE PROCEDURE p_update_fields (par_id       IN VARCHAR2,
  2                                               par_reset_1  IN NUMBER,
  3                                               par_reset_2  IN NUMBER,
  4                                               par_reset_3  IN NUMBER)
  5  IS
  6  BEGIN
  7     UPDATE table_name
  8        SET field1 = CASE WHEN par_reset_1 = 1 THEN NULL ELSE field1 END,
  9            field2 = CASE WHEN par_reset_2 = 1 THEN NULL ELSE field2 END,
 10            field3 = CASE WHEN par_reset_3 = 1 THEN NULL ELSE field3 END
 11      WHERE id = par_id;
 12  END;
 13  /

Procedure created.

Testing: let's reset just FIELD1 for ID = 'A123':

SQL> BEGIN
  2     p_update_fields (par_id       => 'A123',
  3                      par_reset_1  => 1,
  4                      par_reset_2  => 0,
  5                      par_reset_3  => 0);
  6  END;
  7  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT * FROM table_name ORDER BY id;

ID       FIELD1     FIELD2     FIELD3
---- ---------- ---------- ----------
A123                   200        300     --> FIELD1 is now NULL
B456          1          2          3

SQL>