Paurths
asked on
Do a loop inside a stored procedure
Hi experts,
is it possible to do a loop inside a stored procedure?
E.g.:
assuming my sp receives 2 integers (@iC1 and @iC2)
(there are other parameters as well, but they don't matter for the example)
Now, lets say @iC1 = 5 and @iC2 = 17
Then i would like a loop that creates the amount of records
For @iCT = @iC1 To @iC2
Insert into ....
Next @iCT
Can this be done somehow?
G's
Ricky
is it possible to do a loop inside a stored procedure?
E.g.:
assuming my sp receives 2 integers (@iC1 and @iC2)
(there are other parameters as well, but they don't matter for the example)
Now, lets say @iC1 = 5 and @iC2 = 17
Then i would like a loop that creates the amount of records
For @iCT = @iC1 To @iC2
Insert into ....
Next @iCT
Can this be done somehow?
G's
Ricky
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi RogerSTHLM,
thanx for you comment
will test it out in a sec.
Is using a loop (and for each item creating a record) in my program (VB) faster than doing the loop in the stored procedure?
G's
Ricky
thanx for you comment
will test it out in a sec.
Is using a loop (and for each item creating a record) in my program (VB) faster than doing the loop in the stored procedure?
G's
Ricky
No, it's better to do it one connection using a sp.
I just wanted to mention that it is bad to overuse loops.
An example... Don't do like this
1. Get a set
2. [start loop]
3. Manipulate the data in some ways, eg add some chars
3. [end loop]
This should be done when you grab your data in the first place if possible, or be handled in your business code.
Cheers
R
I just wanted to mention that it is bad to overuse loops.
An example... Don't do like this
1. Get a set
2. [start loop]
3. Manipulate the data in some ways, eg add some chars
3. [end loop]
This should be done when you grab your data in the first place if possible, or be handled in your business code.
Cheers
R
try this....
While @iC1<=@iC2
begin
Insert into ....
set @iC1=@iC1+1
end
U should not run this process 1 by 1 from VB for better perfrmance. You can create a batch and then can run that batch in a single go.
While @iC1<=@iC2
begin
Insert into ....
set @iC1=@iC1+1
end
U should not run this process 1 by 1 from VB for better perfrmance. You can create a batch and then can run that batch in a single go.
>> Is using a loop (and for each item creating a record) in my program (VB) faster than doing the loop in the stored procedure? <<
Probably not in this case - as you are looping round and performing an INSERT for each iteration, doing the loop in VB would mean multiple calls to the database. Whereas doing it in a stored proc would mean only 1 call to the DB
Probably not in this case - as you are looping round and performing an INSERT for each iteration, doing the loop in VB would mean multiple calls to the database. Whereas doing it in a stored proc would mean only 1 call to the DB
In my suggestion, you loop in VB BUT dont make the call to database immediately. Create a variable and store all the commands and then run in one connection
for example:
dim d as string
d="EXEC SPName Param1,param2
EXEC SPName Param1,param2
EXEC SPName Param1,param2
EXEC SPName Param1,param2
EXEC SPName Param1,param2
"
and then execute the d variable for better performance
for example:
dim d as string
d="EXEC SPName Param1,param2
EXEC SPName Param1,param2
EXEC SPName Param1,param2
EXEC SPName Param1,param2
EXEC SPName Param1,param2
"
and then execute the d variable for better performance
Try this:
DECLARE @iCT INTEGER
DECLARE @iC1 INTEGER
DECLARE @iC2 INTEGER
SET @iC1 = 5
SET @iC2 = 17
SET @iCT = @iC1
WHILE (@iCT <= @iC2)
BEGIN
INSERT INTO.....
SET @iCT = @iCT + 1
END