How put a condition in the where clause in the procedure?

Question

I want to put a condition in the where clause according to the null condition of the parameter in the procedure. But the procedure I made has some problem. There's problem is in IF clause.

How I can put a condition in the where clause in the procedure?

CREATE OR REPLACE PROCEDURE SP_PROCEDURE(START_DATE, END_DATE)
    IS
      START_DATE DATE;
      END_DATE DATE;
    BEGIN
      START_DATE := TO_DATE(START_DATE, 'YYYYMMDD');
      END_DATE := TO_DATE(END_DATE, 'YYYYMMDD');
    
      INSERT INTO USER
        (
          USR_KEY,
          USR_NAME
        )
      SELECT
        USR_KEY,
        USR_NAME
      FROM
        USER
      WHERE
        1 = 1
        IF START_DATE THEN --I think there's problem here..
          AND USR_CRT_DATE >= START_DATE
        END IF;
    
    COMMIT;
    
      EXCEPTION
      
        WHEN OTHERS THEN
            ROLLBACK;
    END;

Does this answer your question? [Oracle SQL - How to build where clause with optional search parameters](https://stackoverflow.com/questions/30454886/oracle-sql-how-to-build-where-clause-with-optional-search-parameters) — astentx, Nov 11 '22 at 05:00

score 0 · Accepted Answer · answered Nov 11 '22 at 03:34

0

If I'm reading your requirements correctly, this should do the trick

CREATE OR REPLACE PROCEDURE SP_PROCEDURE(START_DATE, END_DATE)
    IS
      START_DATE DATE;
      END_DATE DATE;
    BEGIN
      START_DATE := TO_DATE(START_DATE, 'YYYYMMDD');
      END_DATE := TO_DATE(END_DATE, 'YYYYMMDD');
    
      INSERT INTO USER
        (
          USR_KEY,
          USR_NAME
        )
      SELECT
        USR_KEY,
        USR_NAME
      FROM
        USER
      WHERE (USR_CRT_DATE >= START_DATE
             or START_DATE is null );
    
    COMMIT;
    
    END;

You don't need that exception handler. By default if a PL/SQL unit fails, it will rollback changes to the commencement point of the procedure.

answered Nov 11 '22 at 03:34

Connor McDonald

10,418
1
11
16

Thanks!. The problem has been solved perfectly. I admire your knowledge. – namyulbae Nov 11 '22 at 07:05
1

@namyulbae **NEVER** use `TO_DATE` on a value that is already a `DATE` (as it will implicitly convert it from the date to a string and then back to a date and is likely to introduce errors in the conversion). If you want to remove the time component then use `TRUNC`. Additionally, the procedure's signature is missing the data types for the arguments and using `COMMIT` is a procedure is considered bad practice as it does not allow you to chain multiple procedures together and, if the last one fails, then `ROLLBACK` them all (instead `COMMIT` in the transaction that calls the procedure). – MT0 Nov 11 '22 at 09:18

score 0 · Answer 2 · answered Nov 11 '22 at 09:32

You are missing the data types in the signature of the procedure.
Never use TO_DATE on a value that is already a DATE (as it will implicitly convert it from the date to a string and then back to a date and is likely to introduce errors in the conversion). If you want to remove the time component of a date then use TRUNC instead of trying to use TO_DATE.
It is bad practice to use COMMIT in a procedure as it does not allow you to chain multiple procedures together and then if one fails you can ROLLBACK them all; instead you should COMMIT in the transaction that calls the procedure.
USER is a keyword and cannot be used as an unquoted identifier; if you try you will get the exception ORA-00903: invalid table name. Best practice would be to name the table something that is not a keyword but you could also use quoted identifiers (which is not considered best practice).

CREATE OR REPLACE PROCEDURE SP_PROCEDURE(
  START_DATE IN DATE,
  END_DATE   IN DATE
)
IS
BEGIN
  INSERT INTO "USER" (
    USR_KEY,
    USR_NAME
  )
  SELECT USR_KEY,
         USR_NAME
  FROM   "USER"
  WHERE  (START_DATE IS NULL OR USR_CRT_DATE >= TRUNC(START_DATE));
END;
/

fiddle

How put a condition in the where clause in the procedure?

2 Answers2