| Author |
Topic |
|
ankurgupta26
Starting Member
32 Posts |
 Posted - 2002-10-10 : 17:01:30
|
| Hi,I have a procedure that accepts name as a parameter and does some processing.All the names are stored in table called emp. I need to execute the procedure for all names in the emp table.The only way i can think of is to create a cursor on emp table to get data and then execute the procedure for each name.Is there any way that will let me avoid creating a cursor.Thanks !AnkuR. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 17:06:43
|
| Can you post the code of the procedure you need to execute? |
 |
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-10 : 17:22:17
|
| The problem is that I cant change the procedureThanks !AnkuR. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-10 : 17:25:29
|
| Well then... cursor time. The only way to make it not rely on a cursor would be to go right into the procedure (I think so atleast)-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 17:26:58
|
| OK, I didn't SUGGEST changing the procedure, only that you post the code so I have some way of seeing what it does. It's extremely difficult to say if something can be done without any clue as to WHAT IT DOES NOW. |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-10 : 17:57:03
|
| say.....If the code is as follows create procedure copyempdata (@p_empname as varchar(10))as insert into target_emp select * from emp where ename = @p_empnameThis is just an example procedure. The actual procedure from the production takes plant names as parameter and dumps data to another table based on date, plant name and does some processing, inserts staus in another table etc...As I said i can't change the procedure.Is there any way out. Thanks - all you guys !!Thanks !AnkuR. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-10 : 18:38:22
|
Don't change it,write another one and use it, instead of calling the old one.This will give you experience in what the old one is doing and now you will have two ways of modifing data (Bulk or Single). Edited by - ValterBorges on 10/10/2002 18:39:07 |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-10 : 18:44:24
|
| I can only use the existing procedure....Thanks !AnkuR. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-11 : 04:46:44
|
| Then you can only call it with one row at a time.There is no need to crate an explicit cursor though you can just loop through the primary key on emp.declare @pk , @maxpkselect @pk = 0/'', @maxpk = max(pk) from empwhile @pk < @maxpkbeginselect @pk = min(pk) from emp where pk > @pkselect @par1 = par1... from emp where pk = @pkexec sp @par1...end==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-11 : 13:31:53
|
| Thanks. This seems to be a very good work around. Will try it & let you know the results. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-10-12 : 02:33:19
|
| I don't know, a cursor might beat you on that while loop depending on what data is needed for parameters. Granted, the server is likely to be doing some really aggressive spooling with this one and thus nullifying the advantages of using a cursor.Of course, the process could have already been done if you'd implemented the cursor and ran it instead of posting asking about it. :) I mean, seriously. If you can't create a new procedure, then what's the point? It's a script to be ran every once in a while, and why bother optimizing it? It costs more of your time to find out how to do it only slightly more optimized, than it would've cost to just do it. At a certain point, you have to stop worrying about how it will perform and just Do It so you can see how it performed.Of course, I'd do this in VB 6 using the data designer. Create a select statement that returns all of the parameters, then create a child command that calls the stored procedure and link the columns to the parameters. Then just call the method to run the select statement and *poof*, it does it all for you. Of course, I'm a particularly lazy coder and prefer to have the system do everything it possibly can for me before touching the code window :)(J/K, actually, now that I've been using .NET I can't remember when the last time I've done any VB work.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
Avoiding a cursor