CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
David Faizulaev
David Faizulaev

Reputation: 5741

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

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.

Upvotes: 0

Views: 3738

Answers (3)

rpy
rpy

Reputation: 4023

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

Upvotes: 3

Niall
Niall

Reputation: 66

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)

Upvotes: 4

Chrisrs2292
Chrisrs2292

Reputation: 1094

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;

Upvotes: 0

Related Questions