Multiple Variable If Statement

Question

I have recently been able to produce a procedure where if a variable is not set I can set it to null. Now I am now looking to have multiple variables, but if a value has not been set to that variable, for it then to return all rows.

BEGIN

DECLARE ps_Project_Leader VARCHAR(15);
DECLARE ps_RD_Plan VARCHAR (15);
DECLARE ps_Approval_Status VARCHAR (15);
DECLARE ps_Design_Plan VARCHAR (15);

        SET ps_Project_Leader = ifnull(Project_Leader,null);
        SET ps_RD_Plan = ifnull(RD_Plan,null);
        SET ps_Approval_Status = ifnull(Approval_Status,null);
        SET ps_Design_Plan = ifnull(Design_Plan,null);

 SELECT pp.pid,
    pp.description,
    pp.approval_status,
    pp.design_plan,
    pp.rd_plan,
    pp.estimated_completion,
    pp.project_leader,
    pp.actual_completion
 FROM project_register pp

WHERE pp.project_leader =Project_Leader
        OR Project_Leader is null

and pp.rd_plan =RD_Plan
        OR RD_Plan is null

and pp.approval_status = Approval_Status
        OR Approval_Status is null

and pp.design_plan = Design_Plan
        OR Design_Plan is null

    and 
    PP.actual_completion is null;

end

For instance if i have set 2 of the variables and not the other 2, I do not want it to search on the variables that have not been set.

Many Thanks in advance, if i have not made complete sense (i am new to this so i appologies) I will be happy to clear things up.

there's no difference between IFNULL(var, NULL) and just using var by itself. — Barmar
– Barmar, Commented Mar 31, 2014 at 22:18
What i am looking for this to do though, is if the input is null, is to return all rows. — user3482471
– user3482471, Commented Mar 31, 2014 at 22:21

Barmar · Accepted Answer · 2014-03-31 22:44:46Z

1

You need to parenthesize your WHERE expression correctly:

WHERE (pp.project_leader = ps_Project_Leader
        OR ps_Project_Leader is null)
and (pp.rd_plan = ps_RD_Plan
        OR ps_RD_Plan is null)
and (pp.approval_status = ps_Approval_Status
        OR ps_Approval_Status is null)
and (pp.design_plan = ps_Design_Plan
        OR ps_Design_Plan is null)
and PP.actual_completion is null;

because AND has higher precedence than OR.

edited Mar 31, 2014 at 22:44

answered Mar 31, 2014 at 22:27

Barmar

788k57 gold badges554 silver badges668 bronze badges

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

3 Comments

user3482471 Over a year ago

Ahh brilliant, truly am grateful for this.

spencer7593 Over a year ago

@Barmar: If the database collation isn't case sensitive (and mine never are), those references to variable names won't be seen as references to variable names; MySQL will see them as references to column names.

Barmar Over a year ago

I've changed it to use the ps_XXX variables that he assigned earlier.

spencer7593 · Accepted Answer · 2014-03-31 22:35:15Z

You aren't referencing the local variables, only the procedure arguments. (It doesn't look like you actually need local variables.)

I prefer to use parens around the AND and OR predicates, even if they aren't required. I never have to lookup if AND or OR takes precedence when I use parens, because it doesn't matter, because I'm always specifying the precedence.

I'd help the reader out, and format my SQL like this:

WHERE ( pp.project_leader  = Project_Leader  OR Project_Leader   IS NULL )
  AND ( pp.rd_plan         = RD_Plan         OR RD_Plan          IS NULL )
  AND ( pp.approval_status = Approval_Status OR Approval_Status  IS NULL )
  AND ( pp.design_plan     = Design_Plan     OR Design_Plan      IS NULL )

That way, each line is a "check" of a single column, which is either enabled (with a non-NULL value) or disabled with NULL value.

Really just personal preference, I just find it easier to read that way, even if the line is a little bit longer, I'd rather have the check all one one line.

Again, the local variables aren't needed.

But, you could just set local variables equal to the parameter values, and then reference the local variables in your SQL statement. That really helps out when a variable has the same name as a column, because if the are named the same, MySQL is going to assume it's a reference to column name rather than a variable name. Using a local variable gives you a chance to rename it so it won't be confused with a column name.

UPDATE

I just noticed that the parameter variables names ARE the same as the column names, and that's going to be a problem.

You want your variable names to be DIFFERENT than the column names. You want to make sure that the datatypes of the variables match the columns... later, when you change a column from VARCHAR(15) to VARCHAR(30), you'll need to revisit the procedure and change the definitions of the procedure arguments as well as the local variables.

BEGIN
   -- local variable names are DISTINCT from any column name
   -- in any table referenced by a query these are used in
   DECLARE ps_Project_Leader  VARCHAR(15);
   DECLARE ps_RD_Plan         VARCHAR(15);
   ...

   -- copy parameter values to local variables
   SET ps_Project_Leader = Project_Leader  ;
   SET ps_RD_Plan        = RD_Plan         ;
   ...

   -- query references local variable names 
   ...
    WHERE ( pp.project_leader  = ps_Project_Leader  OR ps_Project_Leader   IS NULL )
      AND ( pp.rd_plan         = ps_RD_Plan         OR ps_RD_Plan          IS NULL )
   ...

Ahh yeah i see that, as all the columns are fetched from the one table the local variables are irrelevant. Why do the variables NEED to be different to the columns names? Surely it makes sense for them not be the same so its more organised and easier to reference? Thanks in advance.

Collectives™ on Stack Overflow

Multiple Variable If Statement

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related