Custom Operations and Stored Procedure Calls

Question

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

At the last stage, he updates the status of some students according to some criteria.

Everything is pretty straight forward, but I have a dilemma. Basically, there is an existing sp in a system called

pUpdateStudentStatus(studentID, statusID, comments, userID) 

This sp is used every time the status of one user is updated. In addition to updating status, it also logs changes to the StudentStatusHistory table.

So here is my dilemma,

• If I want to use this stored procedure, I need a loop through the records (either using the cursor, or by writing a loop)
• If I want all operations to be set based, I need to copy the logic from pUpdateStudentStatus (which may change in the future)

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

I believe that an alternative approach with an update trigger is not a way, because I need additional data, such as the userId of the user who changed the status, and comments

I am using SqlServer2005

Answer 1

You are not saying whether pUpdateStudentStatus under your control or is created by a third party.

If this is a third-party SP, I don’t think you have much choice but to use the cursor / loop, since the internal parameters of the SP may change in future versions.

If SP is under your control, another option would be to create a version of pUpdateStudentStatus with a new name that will work in typing mode (possibly taking a table variable of arguments) write the existing pUpdateStudentStatus to act as a wrapper calling a new procedure with one row in the table arguments.

Answer 2

Personally, if performance is not a problem (and it looks like this is most likely to be such work that will be performed occasionally and, possibly, even planned outside working hours), I would go over the existing procedure. A processor is always cheaper than DBA / Programmer time, and service considerations should redefine efficiency if it does not affect the business without doing so. In any case, you should document why you took any approach that you chose in the code.

Also, if you don't already have a documentation mode, I would suggest setting up a simple documentation table in the database with (at least) the name sp and descriptive text. Due to the nature of the stored procedures / user functions that allow you to view control over what functionality was implemented, where it can be difficult if no strategy is adopted, and I have seen too many databases where there are a lot of stored procedures / udfs and no, what method is implemented somewhere. Version control and full documentation should be welcomed if your group supports it, but if this is not possible, documenting the database within yourself is a simple, reliable and quick win.

Answer 3

If you want to save a set-based operation, then yes, unfortunately, you will need to copy and paste sql from pUpdateStudentStatus .

You will need to decide between the performance of a set-based update, on the one hand, and code reuse (and ease of maintenance), on the other. I know that I usually choose, but your choice depends on your need for performance and other considerations.

Answer 4

If you make a small number of entries, a loop is acceptable, but if batch processes ever get large, you will need set-based code.

Another alternative to what others have suggested if you need set-based logic is to modify proc to allow either a set or separate inserts. Having made the parameters optional (your GUI will need to be checked to ensure that all the necessary parameters are passed for individual inserts) and adding a parameter for batchnumber, which must be passed to work on a set basis, you can put the logic in one Proc.

If the batch number is NULL, perform the current steps. If it is passed, go to the proc package processing part. For batch processes, the insert of proc can be called by another proc, which generates a new batch number, inserts the information you want to insert into the worksheet, including the batch number. then it uses batchnumebr as input to insert proc.

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