Loop through a recordset in a stored procedure

[GJP55]

Is there a simple method to loop through a table in SQL ?
I am trying to write a stored procedure that takes some field values from a table row and uses them as passed variables in a stored procedure.

I can step through a record set easily using a front end application like access but I want to do this completely in SQL for the time being.

Example:

TestTable contains 3 fields:

ValidationFlag
Criteria
Portfolio

There might be 5 records in there with the same portfolio value and so I would want to call the stored procedure 5 times, each time passing back the different ValidationFLag and Criteria field for that row.

Is this a simple thing to do in SQL ?

Thanks

 

[NoCoolHandle]

The simple way is not to step through rows in a table with SQL server!!

What you need to do is find a statement that causes all your rows to be updated once. This runs faster and has less impact on concurrent activities and logging.

HOWEVER...
you could use a CURSOR (Which i hate - they are almost as evil as null values) or...

The bits in blue are the keys.. (Still less overhead than a cursor and it does give you row by row processing)

declare @name varchar(200)
set @name = ''
while not @name is null
begin
  select @name = [blue]min(Name)[/blue] from sysobjects where type = 'u' and [blue]name > @name[/blue]
-- if you pass the end of the records the value will be null
  if not @name is null
    begin
      print @name
    end
 end

 

[GJP55]

Thats just what I was looking for.

Thanks very much.

 

[SajidAttar]

Can you sp_MSForEachTable

For more details

http://www.databasejournal.com/features/mssql/article.php/1490661

http://www.databasejournal.com/features/mssql/article.php/3441031

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell