Set Based Operations and calling Stored Procedures

Question

I am working on a stored procedure that performs some operations on students in the class

In the last step it updates status of some of the students based on some criteria.

It is all pretty straight forward but I have a dilemma here. Basically there is an existing sp in the system called

pUpdateStudentStatus(studentID, statusID, comments, userID)

This sp is used by the application whenever a status of a single user is to be updated. Apart from updating the status it also logs the changes in the StudentStatusHistory table.

So here is my dilemma,

• if I want to use that stored procedure I need loop through the records (either by cursor or by writing loop myself)
• if I want to keep all operations set based I need to copy the logic from the pUpdateStudentStatus (which may change in the future)

Are there any other options? Which one would you choose?

I believe an alternative approach with the update trigger is not a way to go as I need some extra details such as userId of the user that changed the status, and comments

I am using SqlServer2005

Answer 1

You don't say whether pUpdateStudentStatus is under your control or created by a third party.

If it's a third party SP, I don't think you have a lot of choice but to use a cursor/loop, since the internals of the SP may change in future releases.

If the SP is under your control, another option would be to create a version of pUpdateStudentStatus with a new name which will operate in a set-based fashion (perhaps by accepting a table variable of arguments), then re-write the existing pUpdateStudentStatus to act as a wrapper calling the new procedure with a single row in the argument table.