PL SQL - Stored procedure update column if parameter is not NULL

Question

I've written a stored procedure that updates a table. But I would like to take into account where one or more of the parameters are NULL. In such an instance, I don't want to update the column, I want to leave the existing value as is.

I've tried to use:

UPDATE
      VS_USER_T
  SET
      USR_FIRST_NAME = ISNULL(p_NewUsrFName, @p_NewUsrFName)
  WHERE
      USR_ID = lv_Num_UsrId;

But I get an error on the '@', I'm using Oracle 12c.

This is the procedure call

  PROCEDURE UpdateUser
     ( p_UserId             IN         VS_USER_T.USR_ID%TYPE,
       p_NewUsrFName        IN         VS_USER_T.USR_FIRST_NAME%TYPE,
       p_NewUsrLName        IN         VS_USER_T.USR_LAST_NAME%TYPE,
       p_NewUsrname         IN         VS_USER_T.USR_LOGIN%TYPE)

Please advise how my UPDATE statement should look like, when 'p_NewUsrname ' can be NULL, in which case I want to leave the existing value as is.

Thanks in advance.

Please update your code so we can understand better what does your SP look like. — met.lord
– met.lord, Commented Mar 21, 2016 at 14:44
Where in the Oracle manual did you find the syntax to prefix variables with @? — user330315
– user330315, Commented Mar 23, 2016 at 11:19

Niall · Accepted Answer · 2016-03-23 10:42:15Z

4

ISNULL() is not yet a standard Oracle function (at least in the Oracle 12c version that you say you are using). If is of course possible to write a PL/SQL function called ISNULL() and use that.

For a standard Oracle 12c installation, try using NVL or COALESCE instead.

USR_FIRST_NAME = NVL(p_NewUsrFName, USR_FIRST_NAME)
  or
USR_FIRST_NAME = COALESCE(p_NewUsrFName, USR_FIRST_NAME)

answered Mar 23, 2016 at 10:42

Niall

664 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rpy · Accepted Answer · 2016-03-23 11:18:47Z

3

To keep the existing value you need to refer to the existing column value:

USR_FIRST_NAME = ISNULL(p_NewUsrFName, USER_FIRST_NAME)

or you could use:

USR_FIRST_NAME = CASE WHEN p_NewUsrFName is null THEN USER_FIRST_NAME ELSE NewUsrFName END

edited Mar 23, 2016 at 11:18

answered Mar 21, 2016 at 14:54

rpy

4,0432 gold badges22 silver badges31 bronze badges

4 Comments

MT0 Over a year ago

Oracle does not have an ISNULL function: ORA-00904: "ISNULL": invalid identifier. See @Niall answer for some Oracle functions that do the same thing.

rpy Over a year ago

You may either use NVL() or use the more verbose but common CASE WHEN...THEN..ELSE...END construct

Mohammad Faisal Over a year ago

I am using CASE WHEN... with named query but I am getting an error ORA-00932: inconsistent datatypes

rpy Over a year ago

I#d checking the types of the THEN and ELSE legs. But it may be worth an explicit question.

Chrisrs2292 · Accepted Answer · 2016-03-21 14:43:29Z

0

You could use a decode statement e.g.

update my_table t
set    username = decode(p_NewUsrname, NULL, t.username, p_NewUsrname)
where  t.id = p_UserId;

answered Mar 21, 2016 at 14:43

Chrisrs2292

1,1041 gold badge12 silver badges23 bronze badges

Collectives™ on Stack Overflow

PL SQL - Stored procedure update column if parameter is not NULL

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related