Is this a logical use of MySQL Stored Procedures?

Question

I have a client with a hosted MySQL database whose developer keeps asking me to add really simple stored procedures. I look at a stored procedure like this, and I don't see any reason why it would be implemented as a stored procedure and not implemented within application code. Am I correct that this is really strange use of stored procedures?

CREATE DEFINER = 'username'@'%' PROCEDURE `sp_get_payrollgl`(IN pi_glcode TEXT)
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN

  if (pi_glcode is null || pi_glcode = '') then
    select  glcode,descr, 
        case when crdb = 1 then 'CR' else 'DB' end as 'crdb',  
        case when taxable = 1 then 'Yes' else 'No' end as 'taxable',  
        case when billable = 1 then 'Yes' else 'No' end as 'billable',  
        case when active = 1 then 'Yes' else 'No' end as 'active'  
    from payrollgl;
  else
    select glcode,descr, 
        case when crdb = 1 then 'CR' else 'DB' end as 'crdb',  
        case when taxable = 1 then 'Yes' else 'No' end as 'taxable',  
        case when billable = 1 then 'Yes' else 'No' end as 'billable',  
        case when active = 1 then 'Yes' else 'No' end as 'active'  
    from payrollgl where glcode = pi_glcode;
  end if;
END;

Answer 1

For a simple routine like that, I'd say that's a resounding no. There are better ways to achieve the object of this specific store procedure, for instance, having it predefined in a configuration file and/or stored as an array object with the definitions already in place.

Personally I implement stored procedures when there is a complex set of instructions that need to happen in the back end, specifically manipulating large amounts of data across different tables. The benefit is obviously that you don't have too many roundtrips and connections to the database to minimize the overhead you would experience otherwise.

I find that sometimes I use stored procedures to define business logic to encapsulate the intricate details or inner workings from a say, a front-end developer. Another benefit is that if you do you design your system correctly, using stored procedures can make your system a lot more scalable than you'd think possible.

It especially helps when you are doing development across different environments like Development to QA to UAT. If you have to change a line of code in a service somewhere, you to remove it and redploy for the change to go live, which can of course cause a lot of disruptions. With store procedures you can simply alter it and you're on you way. Good luck!