Export Multiple Query Results to Single Excel File

There are probably a number of ways to solve this problem. I would address the issue by attempting to merge the many result sets from your stored procedure calls into a single result set and then perform whatever output-export (to excel) that you wish to do.

Simplest method would be to use a temp table to accumulate the results from each stored proc call. You can use the "INSERT #temptable EXEC mystoredproc @param1" syntax to store the results of a stored proc.

Here's a little example I whipped up:

-- *** Create a sample stored proc that returns one result set ***
CREATE PROC spGetCompanyEmployees @pCompanyID AS INT
AS
BEGIN
    SELECT Company.CompanyName
        , Department.DepartmentName 
        , Employee.EmployeeName
    FROM Company 
        LEFT JOIN Department ON Department.CompanyID = Company.CompanyID
        LEFT JOIN Employee ON Employee.DepartmentID = Department.DepartmentID
    WHERE Company.CompanyID = @pCompanyID
END
GO


-- *** Demonstrate how to call that stored proc multiple times, 
-- *** accumulating the results in a temp table and selecting
-- *** the combined results at the end.

CREATE TABLE #ttbl
(
    CompanyName NVARCHAR(60)
    , DepartmentName NVARCHAR(60)
    , EmployeeName NVARCHAR(60)
)

INSERT  #ttbl
  EXEC spGetCompanyEmployees 1

INSERT  #ttbl
  EXEC spGetCompanyEmployees 2

SELECT * FROM #ttbl

The resulting output from that final SELECT will be a combined, single result set from both stored procedure calls.

I hope this helps.

I combined the suggestion to use a temp table with an example from this blog post that uses sys.sp_msforeachdb to iterate through databases, resulting in the following:

Create TABLE #temp
(
    DbName NVARCHAR(50),
    TableName NVARCHAR(50)
)

INSERT #temp

EXEC
    sys.sp_msforeachdb 
    'SELECT ''?'' DatabaseName, Name FROM [?].sys.Tables WHERE Name LIKE ''%sometablename%'''

Select * from #temp