CODEWITHSUNDEEP
mysqlsql
user3482471
user3482471

Reputation: 227

Multiple Variable If Statement

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.

Upvotes: 0

Views: 392

Answers (2)

Barmar
Barmar

Reputation: 781974

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.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108480

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 )
   ...

Upvotes: 0

Related Questions