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");
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>");
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>");
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).
But ... where is it? Well, you only have to google "doodle ghost" to find it... but, come on... lazy people... I leave you the link to the doodle page and the game... in full screen. Google Doodle Ghost page Direct to Ghost 2022
