RE: Multiple Stored Procedure Execute Together - How?

From: mark baekdal (markbaekdal_at_discussions.microsoft.com)
Date: 02/03/05 

Date: Wed, 2 Feb 2005 22:43:01 -0800

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
 

"GJ" wrote:

> 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.
>
> Thanks in advance.

Relevant Pages

  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... MSDE either from the Setup or from the SQL2KDeskSP3 execute. ... ConfigSamples execute file that they sent me to create the samples ... it does look like some of the sample databases have been ...
    (microsoft.public.sqlserver.msde)
  • Re: Portable stored procedures
    ... > JDatastore lets you store binary streams outside the table scema. ... Other databases usually do not store BLOB's ... > you will have to write the function in the client in Java (or whatever ... >> stored procedures written in some proprietary language. ...
    (comp.lang.java.databases)
  • 2nd Post - Trouble Getting VS.Net 2003 WalkThrough working
    ... the server. ... MSDE either from the Setup or from the SQL2KDeskSP3 execute. ... it does look like some of the sample databases have been ... >> and they directed me to install MSDE and they attached a ConfigSamples ...
    (microsoft.public.sqlserver.msde)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... having executing Sysbase stored procedures via JDBC. ... stored procedure which contains DDL and with AutoCommit set to false I ... The explanation for this behaviour I have found is that the JDBC ... I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)