I've got 5 stored procedures that return recordsets and I want to create a final stored procedure that combines the data returned from those 5 stored procedure into a single recordset, the way a UNION ALL query would. For instance- usp_1 returns users in Virginia, usp_2 returns users in Maryland, and so on... and usp_All would return all users from all 5 states.

What's the best way to do this? Create a temp table and insert each recordset into it? Is there a way to use a UNION ALL with stored procedures within a stored procedure?

Thanks in advance!
BreakerM