Looping in Stored Procedure 1

[Elton1984]

Hiz everyone,
i would like to know if loops(For Loop, While Loop) are allowed in a Stored Procedure.
If it is possible, an example of the codes would be of great help..

Thanx in advance
Elton Wong

 

[venur]

Hi,

Loops are allowed in the Stored Procedure. What DB are you using? as the syntax differs between DataBases.

if you are using Oracle or MySQL the syntax for the loops would be similar to java. If you are SQL Server it would be like VB Script.

Cheers,
Venu

 

[Elton1984]

I am using currently using SQL Server..
The syntax would be something like VB Script?
Is it possible to haf an example of the syntax? Thanx

 

[venur]

Hi,

I do have SQL server on my PC. May be some one will help you much better. But I am 100% sure that MS SQL syntax are similar to VB

Hope this will help you

Eg:
declare 
  @stmt varchar(8000),
  @i int
set @stmt = 'Hello'

set @i = 2
while @i <= 50 
BEGIN
  set @stmt = @stmt + 
          'Line Number is ' + i
  set @i = @i + 1
END
print @stmt

Cheers
Venu

 

[Elton1984]

Thanx for that post of urs venu..
But i am still not able to loop thru the all the records in the DB. My stored procedure onli update the last record of the DB.

DECLARE @prdtID varchar(10)
SELECT @prdtID = a.PrdtCode FROM tblPrdt a LEFT OUTER JOIN tblLoan b ON a.PrdtCode = b.PrdtCode WHERE (b.LoanIndex IS NULL)
UPDATE tblPrdt
SET PrdtStatus = 'On Loan'
WHERE PrdtCode = @prdtID

I know that i am needed to input a loop somewhere in my codes but i seriously have no idea of where and how to do it.. Ermz anyone haf any ideas?

Thanx in advance
Elton Wong

 

[venur]

Hi,

I think you need to use REF Cursor for that, But I am not sure about it. Post the same question in MS SQL server in Tek-tips you will get a very good help.

Cheers
Venu