CODEWITHSUNDEEP
mysqlsqlstored-procedures
user3482471
user3482471

Reputation: 227

MYSQL if parameter null then set

I am new to MYSQL but have a basic understanding of SQL, and i am trying to say that if my parameter has not be set (ie null) then set it as JM.

What/where am i going wrong with the below script?

Thanks in advance

CREATE DEFINER=`james`@`%` PROCEDURE `Project_Status`(IN engineer VARCHAR (5))
BEGIN

SELECT 
    PP.pid_full,
    PP.description_small,
    PP.project_status,
    PP.est_comp_date as 'Est Comp Date',
    EP.engineer
FROM 
    project_pid pp
JOIN
    eng_project_hours EP on PP.pid_full = EP.pid_full

where engineer = ep.engineer;

IF engineer is null then update engineer = jm
set engineer = ep.engineer;
end if;

end

Upvotes: 0

Views: 14211

Answers (3)

spencer7593
spencer7593

Reputation: 108380

I'm not sure I understand what you are trying to do.

If you want to use a value of 'JM' instead of NULL when the input argument has a value of NULL, one way to get that kind of behavior is to declare a local variable in the procedure.

Check the value of the parameter, and set the value of the local variable accordingly; set the local variable to the literal value 'JM' when the input argument is null, or to the value of the input argument when it's not null. Then reference the local variable in the SQL statement.

Something like this:

DELIMITER $$

CREATE DEFINER=`james`@`%` PROCEDURE `Project_Status`(IN engineer VARCHAR(5))
BEGIN

   DECLARE ls_engineer VARCHAR(5);
   SET ls_engineer = IFNULL(engineer,'JM');

   SELECT pp.pid_full
        , pp.description_small
        , pp.project_status
        , pp.est_comp_date as 'Est Comp Date'
        , ep.engineer
     FROM project_pid pp
     JOIN eng_project_hours ep
        ON pp.pid_full = ep.pid_full
     WHERE ep.engineer = ls_engineer ;
END$$

Note that this:

   SET ls_engineer = IFNULL(engineer,'JM');

is easier-to-read shorthand equivalent to this:

   IF ( engineer IS NULL ) THEN
      SET ls_engineer = 'JM';
   ELSE
      SET ls_engineer = engineer;
   END IF;

FOLLOWUP

Q: Say for instance, where i have 'JM' i wished for the procedure to select all the records, is that something that can be done within this?

A: Yes. Let's say, for example, if the input parameter has the "special value" of 'JM', you don't want any restriction on the ep.engineer column at all, you could tweak the query by adding an OR condition to the WHERE clause...

WHERE ep.engineer = ls_engineer
   OR engineer = 'JM'

If the input parameter engineer has a value of 'JM', the predicate following the OR is is going to return TRUE for all rows, so it won't matter whether the part before the OR returns TRUE, FALSE or NULL, the overall result of the WHERE clause is going to be TRUE for all rows.

But I would suggest that NULL would be a more appropriate than 'JM' as a special "return all rows" value for the the input argument, with no need of a "default" value for the input parameter, i.e. no need to translate a NULL to 'JM'. But that really depends on your use case, but you might consider bypassing the JM default value altogether, and just do something like this in your query:

WHERE ep.engineer = engineer
   OR engineer IS NULL

Q: What is the reason/meaning for the ls_ prefix?

A:

The ls_ prefix is just a Hungarian-style notation I've used since Oracle PL/SQL days; I just found it a convenient way to help keep track of scope, and make for variable names that didn't conflict with other variable names, or with column names in SQL.

In a SQL statement, I can qualify column names to avoid ambiguity, but there's no way to qualify variables (apart from using bind parameters).

And I can define a local variable that has the exact same name as a global variable, and my local variable overrides (hides) the global variable, which is usually not what I want.

I'm not really a fan of Hungarian notation, especially not the Windows style lpszFoo and hwndBar, but the Hungarian notation was a convenience for me in Oracle PL/SQL.

I used first letter to identify scope of the variable, "l" for local, "g" for global, "a" for argument. The next letter was shorthand for the datatype of the variable, "s" for VARCHAR (string) type, "d" for DATE, "n" for NUMBER.

So, "as_" was an argument string, "ld_" was for a local date, etc.

Keeping track of the datatypes was important to avoid unintended implicit data conversions in SQL, and made an explicit conversion that was wrong look "wrong", e.g. there's no need for a TO_DATE() around a "date" or a TO_NUMBER around a number, but there is a need to cast a string to number, etc.

Upvotes: 6

Beauvais
Beauvais

Reputation: 2279

You can use COALESCE to return something except from null:

SELECT 
    PP.pid_full,
    PP.description_small,
    PP.project_status,
    PP.est_comp_date as 'Est Comp Date',
    COALESCE(EP.engineer,'jm')'
FROM 
    project_pid pp
JOIN
    eng_project_hours EP on PP.pid_full = EP.pid_full
WHERE engineer = ep.engineer;

Or with CASE and where JM is another column in EP:

SELECT 
    PP.pid_full,
    PP.description_small,
    PP.project_status,
    PP.est_comp_date as 'Est Comp Date',
    CASE WHEN EP.engineer IS NULL THEN EP.jm ELSE EP.engineer END AS engineer
FROM 
    project_pid pp
JOIN
    eng_project_hours EP on PP.pid_full = EP.pid_full
WHERE engineer = ep.engineer;

Upvotes: 0

Hackerman
Hackerman

Reputation: 12305

Just try this:

SELECT 
PP.pid_full,
PP.description_small,
PP.project_status,
PP.est_comp_date as 'Est Comp Date',
EP.engineer
FROM 
 project_pid pp
JOIN
 eng_project_hours EP on PP.pid_full = EP.pid_full
where ep.engineer = coalesce(engineer,"JM");

Upvotes: 0

Related Questions