Sql-server – SQL Server : execute stored procedure from another stored procedure

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;

I am looking for a way to do this myself (in a way that satisfies me..), but a perfectly valid way to do so is described in this Stack Overflow Q & A:

Calling a Stored Procedure in a Stored Procedure in MySQL

The accepted answer by AhamedMustafaM demonstrates a technique using OUT parameters with the following code:

    CREATE PROCEDURE innerproc(OUT param1 INT)
    BEGIN
     insert into sometable;
     SELECT LAST_INSERT_ID() into param1 ;
    END
    -----------------------------------
    CREATE PROCEDURE outerproc()
    BEGIN
    CALL innerproc(@a);
    // @a gives you the result of innerproc
    SELECT @a INTO variableinouterproc FROM dual;
    END

I'm sure not always the answer for what you are trying to accomplish, but it is certainly an option. While you couldn't get the direct result of what the procedure called, this would help you get individual values, possibly using multiple OUT parameters to get what you need.