>>>Hi,
>>>
>>>I am drafting of a new stored procedure (to replace a VFP class method). Right now I want to decide if I should concentrate on the approach with a SP cursor or While without cursor. I lean towards the While without cursor. However, within the While I will be executing Select top 1 on ever iteration/loop (because each iteration/loop of the While will need to find the oldest record in a table). Even though the table is not too big (of course, depending on a customer database). I am still concerned that having these multiple Select Top 1 is not very efficient. With cursor approach, all records can be selected into the cursor, cursor ordered, and then process each row of the cursor.
>>>
>>>Which approach is better practice (in your opinion)?
>>>
>>>TIA
>>
>>You'd be surprised what you can do with T-SQL and how efficient it is (as long as you have the right indexes).
>>
>>
>>SELECT *
>> FROM Parent
>> OUTER APPLY (SELECT TOP 1 *
>> FROM Child
>> WHERE child_fk = Parent_pk
>> ORDER BY Date DESC, TIME DESC) as child
>> WHERE ....
>> ORDER BY …
>>
>
>Thank you for your input. Do I understand from your message that you would go the route of While instead of creating a cursor in the stored procedure?
I do not know the exact nature of your problem, but I would avoid both using WHILE or using cursor. The example above illustrates how to do this. I would not say you can avoid cursors in all cases, but in most cases writing a SELECT with CROSS / OUTER APPLY could do the same as you're trying to achive with cursors.
Walter,