How to add conditions to WHERE clause with if...else in SQL?

Question

I have a stored procedure that I want to update to include another condition in the WHERE clause based on an optional parameter. Right now I am checking my_id but I want to add a check against store_id but only if it's passed to the stored procedure (which is why I want to check if it's NOT NULL).

This is what I want:

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE (my_id IN VARCHAR2, store_id IN NUMBER DEFAULT null) AS
    BEGIN
        SELECT *
        FROM MYTABLE
        WHERE my_id = @my_id
        //I want to add "AND" clause only if store_id is NOT NULL....something like:
        //IF store_id IS NOT NULL
        // AND store_id = @store_id
    END;

I have tried to google search and also look around but haven't been able to find a way. I tried this but it's showing an error in SQL:

CREATE OR REPLACE EDITIONABLE PROCEDURE MY_STORED_PROCEDURE (my_id IN VARCHAR2, store_id IN NUMBER DEFAULT null) AS
    BEGIN
        SELECT *
        FROM MYTABLE
        WHERE my_id = @my_id
        IF store_id IS NOT NULL
           AND store_id = @store_id
    END;

Example for clarification:

If I call the stored procedure MY_STORED_PROCEDURE(1234), then the query should be:

SELECT * 
FROM MYTABLE 
WHERE my_id = 1234

If I call the stored procedure MY_STORED_PROCEDURE(1234, 987) then the query should be:

SELECT * 
FROM MYTABLE 
WHERE my_id = 1234 AND store_id = 987

Answer 1

You may use the following logic:

CREATE OR REPLACE EDITIONABLE PROCEDURE MY_STORED_PROCEDURE
    (my_id IN VARCHAR2, store_id IN NUMBER DEFAULT null) AS
BEGIN
    SELECT *
    FROM MYTABLE
    WHERE
        my_id = @my_id AND
        (store_id IS NULL OR store_id = @store_id)
END;

Answer 2

I suggest using dynamic SQL.

I assume that your stored procedure needs to do something with the result of the [SQL] query, so the below code is actually a stored function that returns a ref cursor.

CREATE OR REPLACE FUNCTION MY_STORED_FN (my_id IN VARCHAR2, store_id IN NUMBER DEFAULT null)
RETURN SYS_REFCURSOR
AS
  l_ret_val SYS_REFCURSOR;
  l_sql  VARCHAR2(1000);
BEGIN
  l_sql := 'SELECT * FROM mytable WHERE my_id = :i';
  IF store_id IS NOT NULL THEN
    l_sql := l_sql || ' AND store_id = :s';
    OPEN l_ret_val FOR l_sql USING my_id, store_id;
  ELSE
    OPEN l_ret_val FOR l_sql USING my_id;
  END IF;
  RETURN l_ret_val;
END;

Then you could call your function in a [SQL] query, for example:

SELECT MY_STORED_FN(2) FROM DUAL;

Answer 3

The simplest answer to this question could be -

CREATE OR REPLACE EDITIONABLE PROCEDURE MY_STORED_PROCEDURE (my_id IN VARCHAR2, store_id IN NUMBER DEFAULT null) AS
    BEGIN
        SELECT *
        FROM MYTABLE
        WHERE my_id = @my_id
        AND store_id = NVL(@store_id, store_id)
    END;

Answer 4

In Oracle:

• @ before a variable is invalid syntax.
• A bind-variable is prefixed with :; and
• A PL/SQL variable has no prefix.

You are using PL/SQL variables and they should have no prefix.

Which brings a second problem, if you use an identifier in an SQL statement then the query will prioritise the variable from the local scope (i.e. the column names of the table(s) used in the query) and, only if it does not find it, then will it look for PL/SQL variables in the outer PL/SQL scope. So WHERE my_id = @my_id should be WHERE my_id = my_id which is effectively WHERE the_column_from_the_table = the_column_from_the_table and not WHERE the_column_from_the_table = the_argument_to_the_procedure. Therefore, you should never name your variables with the same name as columns in the table you are querying.

Additionally, in PL/SQL you cannot use a SELECT ... FROM ... statement. You either need to use:

• SELECT ... INTO ... FROM ... if your query will return exactly 1 row.
• SELECT ... BULK COLLECT INTO ... FROM ... if your query could return 0 or many rows and then you can collect the results into collection variables (i.e. arrays).
• Open a cursor to return the result set.
• Use a PIPELINED function (rather than a procedure).

To solve your problem:

1. Rename your input parameter to something different to the column name;
2. Don't use @ before the variable;
3. Use AND and OR in the query;
4. Either use SELECT ... INTO ... or open a cursor for the results.

Like this:

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE(
  i_my_id    IN  MY_TABLE.MY_ID%TYPE,
  i_store_id IN  MY_TABLE.STORE_ID%TYPE DEFAULT null,
  o_cursor   OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN o_cursor FOR
    SELECT *
    FROM   MYTABLE
    WHERE  my_id = i_my_id
    AND    (i_store_id IS NULL OR i_store_id = store_id);
END;
/