MySQL - Procedures and Functions with optional parameters

If you use database procedures and functions in MySql or Sql, that receive parameters and that are called from back code (for example, PHP), or from any other language, you will have noticed that, on more than one occasion, if you have decided to add a parameter, you have either created an alternative procedure or function (so that the code already written is not screwed up) or you have had to revise all the back code to add, even if it is empty or null, the parameter in question.

And you may have thought... "SQL could already have, in the style of javascript, php and other languages, the possibility of creating a parameter with a default value in case it is not supplied", that is... an optional parameter. 

For example, we are going to use a very simple procedure that receives two string parameters, returning their values.


  CREATE PROCEDURE localdb.parameters_test(p1 varchar(10),p2 varchar(30) )
  BEGIN
      select p1,p2;
  END


If in this case we do...


	call parameters_test("hello","bye");
    
    
    

    
p1
p2
    
hello
bye
    
   



If we now change the procedure to...



  
  CREATE PROCEDURE localdb.parameters_test(p1 varchar(10),p2 varchar(30),p3 varchar(10) )
  BEGIN
      select p1,p2,p3;
  END
  



But if we do not change the call, we will get an error. 





How can we then use database procedures and functions, where, within their code, we get optional parameters, even of undefined type values? Yes, I know that now the trend is to use data typing at all times, just as from 1998 to 2004 approximately, it was recommended to use Foreign Keys to preserve the integrity of the tables, while later it became fashionable to maintain the integrity of the code and not use the related keys, or Foreign Keys. And now many already use No-SQL to avoid defining a rigid database structure. 

All these types of practices are up to the analyst, who has to take into account whether robust integrity, more agile code development, and a thousand other variables are desired. As an anecdote, I will tell you that many people understood that "now it is not fashionable to create related keys/foreign keys" and ended up not using key indices. You can imagine how many databases I have had to index and reindex because, at a certain volume of data, the query was taking forever. In SQL, please use keys!

That said... to the point.


Optional Parameters in SQL/MySQL

To "simulate" and control parameters in database functions and procedures, we make use of the EXTRACTVALUE command, which is like a "reader" or "interpreter" of XML values. In the following basic example, you will understand it clearly.


  CREATE PROCEDURE localdb.parameters_test(pAll varchar(300))
  BEGIN
      set @p1 = (select ExtractValue(pAll,"p1"));
      set @p2 = (select ExtractValue(pAll,"p2"));
      select @p1,@p2;
  END
  



	Call parameters_test("<p1>Hello</p1><p2>Bye</p2>");
    
    
    
    

    
p1
p2
    
Hello
Bye
    
    


And if we now change the procedure to accept a new parameter, it won't be necessary to change anything in the back (or source or whatever you want to call it) code.


  CREATE PROCEDURE localdb.parameters_test(pAll varchar(300))
  BEGIN
      set @p1 = (select ExtractValue(pAll,"p1"));
      set @p2 = (select ExtractValue(pAll,"p2"));
      set @p3 = (select ExtractValue(pAll,"p3"));
      select @p1,@p2,@p3;
  END
  





	Call parameters_test("<p1>Hello</p1><p2>Bye</p2>");
    
    
    
    

    
p1
p2
p3
    
Hello
Bye
 
    
    


Of course, within the procedure or function, you should check both the type, the value and whether or not the passed parameter exists to make your code understandable and useful. But that is up to you and your team (if any).