locked
Stored Procedure or Function or Cursors RRS feed

  • Question

  • User-1499457942 posted

    Hi

     how to loop through table and perform some calculation and update data . What is better Using Stored Procedure or Function or Cursors.

    Thanks

    Friday, October 26, 2018 11:46 AM

All replies

  • User753101303 posted

    Hi,

    Are you sure you have to explicity loop? Always try to use SQL ie are you sure an UPDATE statement is not enough ?

    Friday, October 26, 2018 4:06 PM
  • User61956409 posted

    Hi JagjitSingh,

    JagjitSingh

    how to loop through table and perform some calculation and update data .

    Normally, Cursors are good used when performing row-by-row operations, if you'd like to verify each row data, and perform calculations, you can use Cursors.

    Note:

    1) As we know, using Cursors can be really slow when performing operations on large number of records.

    2) If possible, please clarify more about your scenario and requirement.

    With Regards,

    Fei Han

    Monday, October 29, 2018 2:39 AM
  • User-595703101 posted

    There are various properties of SQL cursors, if you are looking for performance you can use read only and forward only cursors

    But SQL engine is optimized for set based operations instead of iterations

    Especially with window functions, analytical functions database developers can calculate most required values in a single SELECT statement for all rows. This removes the requirement for looping for each row to make those calculations.

    So, if you could share your requirement, we can suggest more confidently that a cursor can be replaced by set based SQL commands

    Tuesday, October 30, 2018 6:03 AM