Multiple rows output from Stored procedure

[ykfc]

I've used to process multiple rows data return by sql select statement, using vb or Java codes. As usual, it is just looping through the result set and process the data one record at a time.

Can people employ Stored Procedure alone to do row-level processing?

I did some research from the web but couldn't find a illustration how to use Stored procedure alone to accomplish the same task. Other web articles also point me to use VB, ASP, etc.

I did encounter one piece of code (from my workplace) that accomplishes it, but in somewhat weird way:
1) select the first record based on some criteria
2) process the record (for example: use the data to update other table)
3) change certain data of the record it processed so that it will not meet the same criteria in step 1.
4) loop back step 1

The above coding is wierd (in my opinion) because it requires multiple times access to the same table in step 1.. Can someone comment and enlighten me?

 

[Catadmin]

What sort of row level processing are you attempting to do? Just a general update of one column? Are you looking to do inserts? Or are you just wanting the code to return record sets based on a specific value?

Stored Procedures can do row level processing, but what you want it to do depends on what you're looking to do with the data once you have it.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ykfc]

Thanks for reply.

"What sort of processing?"
Retreive certain records. Processing of each of the retrieved record includes:
1) get other data from other database and/or table to retrieve other values
2) use the values as parameters to run Crystal Report
3) update another table in the same/other databases (could not non-SQL
4) log events (with date/time)
5) update column of the row it retreives

Just now I read about the output parameter of the type cursor. But not too sure how to use it.

 

[Catadmin]

Soooo, you want to get a record so it can fetch a different record from a second table/db so the second record can run a Crystal report?

And then you want the Crystal Report to update tables in the other databases?

I'm still confused. Can you please give an example to help me understand your process better?

Thanks,


Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???