Solved

Exec 5 sp at same time

Posted on 2004-08-06
20
489 Views
Last Modified: 2008-02-26
Hi,

I have an ASP summary report that has 5 categories.  I use 5 sps, Is there a way to call them at once and retuen 5 recordset back to ASP? instead of calling DB 5 times?  thanks
0
Comment
Question by:mcrmg
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11738804
Sure.  Try posting your code.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 50 total points
ID: 11738824
it may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp
-----------------------------------
;number

Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11738827
have you tried  just creating a stored procedure to call the other 5 ?

create procedure mainproc
as
exec proc1
exec proc2
exec proc3
exec proc4
exec proc5
return

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:mcrmg
ID: 11738860
@Lowfatspread,

That is what I did.  But it hangs on ASP side.  When I do that, does it return 5 rs or just one?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11738904
should be 5

what are the procs trying to do anyway?

could they be better written as 1 combined procedure?

do they return if called individually?
0
 

Author Comment

by:mcrmg
ID: 11738941
I wish I could  :(

Basically, they have 5 different WHERE clauses.  I need to use different sp.

If it returns 5 rs, how can I get the one that I want?

Thx
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 11739106
sorry which one out of the 5 is the one you want ?  (puzzled)

just because you've got 5 different selection conditions does mean you need 5 different stored procedures...

can the output be unioned?

are the result sets the same format?

then have 1 sp
as

create proc mainproc

as
create table #temp
(
[rowid] int identity(1,1)
define your "output" table layout
)

Insert into #temp ( the columns not including the [Rowid])
 Exec proc1
Insert into #temp ( the columns not including the [Rowid])
 Exec proc2

...

Insert into #temp ( the columns not including the [Rowid])
 Exec proc5

Select  (you columns except [rowin])
from #temp
order by [rowid]

return

 



0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 50 total points
ID: 11739123
This is a tricky thing, because I think this code:

exec proc1
exec proc2
exec proc3
exec proc4
exec proc5

will still run them synchronously, that is, proc1 must end before proc2 starts, proc2 must end before proc3 starts, etc., so they are not running at the same time.

AFAIK the only way in SQL Server to get them to run at the same time from within a single SP is to start jobs that execute each proc, since sp_start_job will return as soon as the jobs starts rather than waiting until it ends.  The main SP then has to wait until all jobs complete, then return the result set(s).

No, this is not necessarily an elegant solution, but it's the only I could come up with in a reasonable period of time when we had the same issue.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11739168
From an ASP standpoint, you could return to the user as soon as the five starts finished.  Then allow the user to check back later to see if the actual results were ready.
0
 

Author Comment

by:mcrmg
ID: 11739187
@ Lowfatspread

Yes, they are in the same output format........
So, I can use the way youy mentionmed above to return one rs.
If they are not in the same output format, that means I have to call db again?

Thx

0
 

Author Comment

by:mcrmg
ID: 11739204
@ScottPletcher,

exec proc1
exec proc2
exec proc3
exec proc4
exec proc5

Is this the way you used?

>From an ASP standpoint, you could return to the user as soon as the five starts finished.  Then allow the user to check >back later to see if the actual results were ready.
How do you distinguish 5 rs in ASP?

Thanks
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 11739246
or massage the output format to get a common one across the result sets

e.g.

if proc1 returns an int as the first column
select convert(char(10),anINTcolumn) as Col1,....

and proc2 returns a character column
select acharactercolumn as col1



in general you'd need to convert then to character based columns

or if you've got varying numbers of columns from the procedures

then you'd either have to define the original table (#temp) columns as nullable...
and not specify them if the proc outputs less

or varieties of

create table #temp
as

(col1
col2
col3
col4
col5
)


insert into #temp
 (col1,col2,col3)
exec proc1
insert into #temp
 (col1,col3,col4,col5)
exec proc2

etc...









0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11739276
Yes scott's correct on the synchronous nature of the execution...
and  starting jobs from within a "control" procedure still leaves you with the problem of
looping around the "output" Tables/Queues to obtain the results...
probably best to return a panel requesting  the user to "page forward" for the next results...  
if that makes sense in your scenario ...
0
 

Author Comment

by:mcrmg
ID: 11739332
>probably best to return a panel requesting  the user to "page forward" for the next results...  
>if that makes sense in your scenario ...


I was wondering if you could spend a min tell me more about this?(I am not fllowing........)   sorry

Thx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11739503
>> and  starting jobs from within a "control" procedure still leaves you with the problem of looping around the "output" Tables/Queues to obtain the results... <<

Actually I was thinking of something like:

SELECT *
FROM ##Result1
UNION ALL
SELECT *
FROM ##Result2
UNION ALL
...

Naturally with any manipulation required to match the column types.

The main SP could create the global temp tables prior to starting the jobs that load them, or, my preference, it could call a separate SP to do that prior to starting the jobs.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11739610
hmm
don't you still have locking problems potentially

but i'd hadn't got the individual global tables part ...

interesting
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11739720
>> just because you've got 5 different selection conditions does [not] mean you need 5 different stored procedures... <<

Excellent point by Lowfatspread.  Are the requests going against the same table(s)?  If so, we might be able to reduce the number of queries if you post the code.
0
 

Author Comment

by:mcrmg
ID: 11740584
They are not using the tables (well, partially are........)
0
 

Author Comment

by:mcrmg
ID: 11740834
I am using this way:


My question is: Is there a need to drop the temp table?


create proc mainproc

as
create table #temp
(
[rowid] int identity(1,1)
define your "output" table layout
)

Insert into #temp ( the columns not including the [Rowid])
 Exec proc1
Insert into #temp ( the columns not including the [Rowid])
 Exec proc2

...

Insert into #temp ( the columns not including the [Rowid])
 Exec proc5

Select  (you columns except [rowin])
from #temp
order by [rowid]

return
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11740958
It is difficult to give you an intelligent answer without seeing your code from your stored procedures or at least an idea of what they do.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question