sql server programming: Hi,

I have multiple stored procedures (SP) running in multiple databases. The
output of all the databases has same column names, same number of columns and
column types.

I want to run all these different SP's as one SP and combine the output as
one result.

I tried creating one new SP as SPAll and calling all the SP's in SPAll.
eg.

Create SPAll @Parameter int
AS
Exec SP1 @Parameter
Exec SP2 @Parameter
GO

But when I execute SPAll from 'sql server reporting services' (vs.net), it
executes and displays results from SP1 also. I want to display results from
both SP1 and SP2.

Hello GJ.

execute the stored procedures saving the result sets to a temporary table
and then select the results from the temporary table to give you a single
result set

IE:

use Northwind
GO

set nocount on

if object_id('tempdb..#results') is not null drop table #results

create table #results(
index_name sysname,/* Index name. */
index_description varchar(210),/* Index description. */
index_keys nvarchar(2078)/* Table or view column(s) upon which the index is
built. */
)

insert #results(index_name,index_description,index_keys)
exec sp_helpindex @objname = 'dbo.Categories'


insert #results(index_name,index_description,index_keys)
exec sp_helpindex @objname = 'dbo.Customers'

select * from #results




regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server




[quoted text, click to view]