[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Do a loop inside a stored procedure

Posted on 2005-04-15
7
Medium Priority
?
398 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Paurths
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 5

Accepted Solution

by:
RogerSTHLM earned 1000 total points
ID: 13789776
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
 
LVL 23

Expert Comment

by:adathelad
ID: 13789786
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
 
LVL 12

Author Comment

by:Paurths
ID: 13789795
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:RogerSTHLM
ID: 13789806
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
 
LVL 1

Expert Comment

by:Amiiit
ID: 13789815
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
 
LVL 23

Expert Comment

by:adathelad
ID: 13789819
>> 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
 
LVL 1

Expert Comment

by:Amiiit
ID: 13789882
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

873 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