Solved

Exec 5 sp at same time

Posted on 2004-08-06
20
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 75

Expert Comment

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

Assisted Solution

by:Eugene Z
Eugene Z 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how the fundamental information of how to create a table.

695 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