How to use values from one stored procedure into another?
For eg I have a SP which give a result when i, CALL A1(); i fetch the result and store in $a. But when I call another SP CALL A2('$a'); It is not executing. Showing Array() when I run the program
When you compose the SQL command
"CALL A2('$a');" in PHP, the parser first expands the
$a variable within your double-quoted string literal. However, as documented under Converting to string:
Arrays are always converted to the string "Array"
Therefore PHP interprets the string that is to be sent to MySQL as
"CALL AS('Array');", which is obviously the root of your problem. Note also that allowing PHP to expand variables into SQL commands in this fashion is dangerous: see @deceze's blog article The Great Escapism (Or: What You Need To Know To Work With Text Within Text) to understand this better; and then How can I prevent SQL injection in PHP? to understand how variables should (generally) be passed from PHP to SQL.
However, in this case, the fix is not so straightforward. Since MySQL does not have an array datatype, even if you did properly serialise the array then it would only recognise the serial form (which will not be easy to use in your
It should also be noted that stored procedures don't really "return" anything. Whilst they can output zero or more resultsets, resultsets can't be used as an input to another stored procedure. The normal workaround is to store the resultset (e.g. in a temporary table) and then later access that as required.
That said, it is very rare indeed that such an arrangement is necessary. Usually people attempt such things because they're trying to use SQL as a procedural language, whereas it is a very powerful declarative language. If you explain what it is that your procedures are collectively trying to do, I very much suspect that someone will be able to write a single SQL command to do the whole thing.