[SOLVED] Assigning from a Stored Procedure within a Stored Procedure

Assigning from a Stored Procedure within a Stored Procedure

This question is not a duplicate. That question is specifically about a secondary procedure that creates a temp table that the first procedure needs to read. This question is about consuming the return of a second procedure. The correct answer was provided by @Barmar - the secondary procedure should be a Function - not a procedure.

I am trying to assign the result of stored procedure call into a local variable within another stored procedure. MySQL is complaining when executing the 'Create' that my syntax is incorrect, but from what I can tell, it should be fine (clearly not, obviously).

The line it is complaining about is set warrantyVersion = call GetActiveDefaultWarrantyTerm();. The call portion works fine when executed from a standard prompt, and it doesn't have a problem with the subsequent call to TargetSerialAtWarrantyTerm.

The error is non-specific - SQL Error [1064] [42000]. I've tried a number of things, including wrapping it in parentheses, selecting the result from the call, set warrantyVersion = cast(call GetActiveDefaultWarrantyTerm() as varchar(16)) (with and without a select, to no avail. What am I missing here?

create procedure AddServiceRecordToProduct(...args...)
begin
    declare warrantyVersion varchar(16);
    
    ...error checking...
    
    -- make it exist
    insert into servicerecord...;
    
    -- create a warranty cross-ref, if necessary
    set warrantyVersion = call GetActiveDefaultWarrantyTerm();
    call TargetSerialAtWarrantyTerm(serialin, warrantyVersion);
end;

and the procedure being called (Version is a varchar(16)).

create procedure GetActiveDefaultWarrantyTerm()
begin
    select w.Version from ... limit 1;
end;

Solution

Thanks to comments for pointing me in the right direction.

The problem is a misconception on my part. Procedures do not return values - they run code. If the code to be run includes a select it can appear as though there is a return value. This is reinforced by ORMs treating it as a return. In reality, the result set of the select is simply a side-effect of the code.

Ultimately, the fix is simple. Stored Functions provide return values. The secondary, nested procedure should be a Function.