| Author |
Topic |
|
pelegk2
Aged Yak Warrior
723 Posts |
 Posted - 2006-07-07 : 02:23:44
|
| i need to run some stored procedure about 20 times each time (where i run a loop of 1 to 20) but with diffrent params.whats the best way to do it?do i need to close or set the connection to nothin g between each loop in asp?thnaks in advancepelegIsrael -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-07 : 02:41:14
|
| what's the parameter (source) and what does your sproc do?--------------------keeping it simple... |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-07 : 02:48:42
|
Why not create another stored procedure to call your stored procedure 20 times ? Will this work for you ?
KH |
|
|
|
pelegk2
Aged Yak Warrior
723 Posts |
Posted - 2006-07-07 : 02:56:26
|
| no kthan i tried that but i gives me lot of messages beack beacuse of call in the sp to other sp as well.what generlly is the correct code for opening and closing a SP?Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-07 : 02:57:48
|
As jen ask. What does your Stored Procedure do ? It is just perform some updates or it will return a result set ?
KH |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-07 : 03:01:08
|
| If you're calling it from ASP, you should be able to create and re-use the ADO Command object:1. Create and instantiate the Command object2. Set the CommandText etc and define the parameters3. Start your loop - populate with params - execute - restart loop4. Then you can kill your command object and close your connection |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2006-07-07 : 04:26:23
|
quote:
Originally posted by timmy
If you're calling it from ASP, you should be able to create and re-use the ADO Command object:1. Create and instantiate the Command object2. Set the CommandText etc and define the parameters3. Start your loop - populate with params - execute - restart loop4. Then you can kill your command object and close your connection
I think there will be round trips to the database server by this method. But why do you need to loop. Send us the code so that we can analyse better.RegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-07-07 : 05:49:44
|
| I almost guarantee that you do not need to "loop through" in this manner. Post what you are trying to do and someone will come up with a solution that's not so wildly inefficient as making multiple trips to the data source.-------Moo. :) |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-09 : 18:16:17
|
quote:
I think there will be round trips to the database server by this method.
You think?There are inefficiencies there, but if peleg is trying to save 20 distinct object to the database, this is the best way to do it IMO. The alternative is to send the data in some custom array into one stored proc and have it do the parsing. Not really efficient or maintainable. As long as you keep and re-use the command and connection objects it should be a reasonable trade-off. Tim |
|
|
|
|
stored procedure using multiple times