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
LVL 12
PaurthsAsked:
Who is Participating?
 
RogerSTHLMCommented:
declare @iCt int

set @iCt=@iC1

while @iCt<=@ic2 begin
  insert into...
  set @iCt=@iCt+1
end

However, you should try to avoid loops (or cursor) in t-sql if possible.

Cheers
R
0
 
adatheladCommented:
Yes.

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
0
 
PaurthsAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RogerSTHLMCommented:
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

0
 
AmiiitCommented:
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.
0
 
adatheladCommented:
>> 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
0
 
AmiiitCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.