Union the Results of Multiple Stored Procedures

UNION the results of multiple stored procedures

You'd have to use a temp table like this. UNION is for SELECTs, not stored procs

CREATE TABLE #foo (bar int ...)

INSERT #foo
exec MyStoredProcedure 1

INSERT #foo
exec MyStoredProcedure 2

INSERT #foo
exec MyStoredProcedure 3

...

And hope the stored procs don't have INSERT..EXEC.. already which can not be nested. Or multiple resultsets. Or several other breaking constructs

How do I combine result sets from two stored procedure calls?

This may be oversimplifying the problem, but if you have control over the sp, just use in rather than =:

CREATE PROCEDURE [dbo].[MyStored]
AS
   SELECT blahblahblah WHERE StoredState IN (0,1) LotsOfJoinsFollow;
RETURN 0

If this is not an option, just push the results of both sproc calls into a temp table:

/*Create a table with the same columns that the sproc returns*/
CREATE TABLE #tempblahblah(blahblahblah NVARCHAR(50))

INSERT #tempblahblah ( blahblahblah )
 EXEC MyStored 0

INSERT #tempblahblah ( blahblahblah )
 EXEC MyStored 1

SELECT * FROM #tempblahblah

Mysql make a stored procedure from multiple stored procedures

As I think above feature is not implemented in MySQL. See here for more information.

I came up with following solution. But not sure it is the best approach to this problem.

DELIMITER //
CREATE PROCEDURE pro_1(IN param1 data_type,IN param2 data_type);
BEGIN
   CREATE TEMPORARY TABLE temp1 AS SELECT * FROM table_name WHERE $where1;
COMMIT;
END //
DELIMITER ;

as above you can create next two procedures as follows.

DELIMITER //
CREATE PROCEDURE pro_2(IN param1 data_type,IN param2 data_type);
BEGIN
   CREATE TEMPORARY TABLE temp2 AS SELECT * FROM table_name WHERE $where2;
COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE pro_3(IN param1 data_type,IN param2 data_type);
BEGIN
   CREATE TEMPORARY TABLE temp3 AS SELECT * FROM table_name WHERE $where3;
COMMIT;
END //
DELIMITER ;

So following is the master procedure which is union of above 3 procedures.

DELIMITER //
CREATE PROCEDURE master_pro(IN param1 data_type,IN param2 data_type);
BEGIN
   CALL pro_1(param1,param2);
   CALL pro_2(param1,param2);
   CALL pro_3(param1,param2);
   CREATE TEMPORARY TABLE master_temp AS (SELECT * FROM temp1) UNION ALL (SELECT *  FROM temp2) UNION ALL (SELECT * FROM temp3);
COMMIT;
END //
DELIMITER ;

SO finally if you want to get any other results from master procedure you can do as follows.

DELIMITER //
CREATE PROCEDURE another_pro(IN param1 data_type,IN param2 data_type);
BEGIN
   CALL master_pro(param1,param2);
   SELECT columns_from_master_temp FROM master_temp WHERE where_clause;
COMMIT;
END //
DELIMITER ;

Passing the results from multiple stored procedures to a view

A ViewModel simply holds all the data a view needs. Make a class to hold the vehicle collection and any other data the view needs and pass it to the page. Then you can strongly type the page with the ViewModel.

public class VehicleViewModel
{
    public ICollection<VehicleModel> VehicleModels { get; set; }
}

public ActionResult Vehicles(int? makeId, int? countryId)
{
    if(!makeId.HasValue || !countryId.HasValue)
    {
        RedirectToAction("Error");
    }

    var models = db.spVehicleGetModels(makeId, false, true, countryId);
    var viewModel = new VehicleViewModel { VehicleModels = models.ToList() };
    return View(viewModel);
}

---

Related Topics