Loops in Stored Procedures!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Loops in Stored Procedures!!!

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-09 : 13:50:56

Hi,

Can somebody give me an example of a loop in sql stored procedure.

say this is what i want to do,

for i = 1 to 10
call procedure1 i
next

Is there anything equivalent to this kind of iterations in stored procedures.

Thanks a lot in advance.
Nitu

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-09 : 14:01:57

[code]DECLARE @I = 1

WHILE(I <= 10)
BEGIN
your procedure goes here.
SET @I = @I + 1
END[/code]

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-09 : 14:07:56

Thank you so much.

I did the same thing but it was going in infinite loop. the difference was, that i had set i = 0.
Now when i set i to 1, its working fine.
i dont understand why it was going in infinite loop before with i=0.

Thank you
Nitu

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-09 : 14:35:39

probably because @i wasn't zero, but rather NULL.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-09 : 14:45:52

quote:
Originally posted by Nitu

Thank you so much.

I did the same thing but it was going in infinite loop. the difference was, that i had set i = 0.
Now when i set i to 1, its working fine.
i dont understand why it was going in infinite loop before with i=0.

Thank you
Nitu

Did you have this before?

DECLARE @I int

WHILE(I <= 10)
BEGIN
   your procedure goes here.
   SET @I = @I + 1
END

Because if you did then like Tara said @I would be null and that would screw up your loop.

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-09 : 15:05:46

Yeah i had the loop before, you might be right about @i being a null.

I have another question, can we use arrays in stored procedures.

Here is example of what i want to do.

select @fieldCount = Count(@field1) from table1 where field2 = @variablepassed_to_sp

"lets say table1 has 6 rows for the condition
field2 = @variablepassed_to_sp"

now i want to process each row,
set @i = 1
while @i <= @fieldCount

select @tempVar(i) = field1 from table1 where field2 = @variablepassed_to_sp

select field3, field6 from table2 where field4 = @tempvar(i)

if field3 <> field6
call procedure3
else
call procedure4
set @i = @i + 1

Can you give me a sample code to do the above.

Thanks a lot in advance,
Nitu

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-09 : 16:44:35

quote:
Originally posted by Nitu
...I have another question, can we use arrays in stored procedures...

SQL Server doesn't have arrays like you are thinking of, but you can use tables:


declare @ARRAY table (
array_index int not null primary key clustered,
array_value int not null,
)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources