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

If you want to keep the operation set-based then yes, sadly, you will need to copy and paste the sql from pUpdateStudentStatus.

You will need to decide between the performance of a set-based update on the one hand, and code re-use (and ease-of-maintenance) on the other. I know which I would normally choose, but your choice depends on your need for performance versus other considerations.

Answer 2

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.

Answer 3

Personally unless performance is an issue (and it sounds like this is most likely the sort of job that will run occasionally and maybe even scheduled outside work hours) I would loop over the existing procedure. CPU is invariably cheaper than DBA/Programmer time and maintenance considerations should override efficiency unless there is an impact on the business by not doing so. Either way you should document why you have adopted whichever approach you choose in the code.

Also, if you don't already have a documentation regime I would suggest setting up a simple documentation table within the database with (at least) sp name and descriptive text. Because of the nature of stored procedures/user defined functions keeping overview control over what functionality has been implemented where can be tricky unless some strategy is adopted and I've seen far too many databases where there is a mass of stored procedures/udfs and no simple method of groking what functionality has been implemented where. Version control and full documentation is to be applauded if your group supports it, but if that isn't available then documenting the database inside itself is simple, robust, and a quick win.

Answer 4

If you are doing a small number of records, looping is acceptable, but if the batch processes ever get big, you will need set-based code.

An other alternative to what other suggested if you end up needing the set-based logic is to change the proc to allow either set-based or individual inserts. By making the parameters optional (your GUI will need to check to make sure all required parameters are passed for individual inserts) and adding a parameter for a batchnumber to be passed in for set-based operation, you can put the logic for both in one proc.

If batch number is null, do the current actions. If it is passed go to the batch processing part of the proc. For batch processses, the insert proc can becalled by another proc that generates a new batch number, inserts the info you want to insert into a work table including the batch number. then it uses the batchnumebr as the input parameter for the insert proc.

You still will have to write the logic for both cases, but since they are in the same proc, they will be easier to maintain and you will be less likely to forget to update both processes.