MSQL - Stored Procedures

[dannabn]

I have serveral stored proces, that each update or insert records into there own tables. They are all called from a VB app one at a time.Is it better to have the VB app call each one, or have the VB app call the first one and have each SP call the next one.. and so on ??

 

[dan1967]

I think it would be better to have the VB app call each one. If each proc called the next, they would all be "open" until the last one was done running. In VB they would each run and end before the next one starts.

 

[SQLSister]

Which would be better I think depends on whether the updates are independent of each other or not. If you want the ability to commit or rollback all the transactions together, it is probably better to have them called from the stored procedure. If these tables do not have a primary key/foreign key relationship executing them independently may be fine. So for instance if I'm updating my airport table and my runway table, I want the whole thing to commit together as every airport must have at least one runway. But if I'm udating my airport table and my tourist attraction table it won;t matter if one update fails as far as updating the other is concerned.

 

[aroggeband]

As Dan1967 noted, chaining procedures causes each procedure to remain pending in memory until the last ends. For this reason it is rarely a good idea. I have seen people cause system crashes by having the last call the first to implement a loop.

Consider SQLSisters response, and if you need to implement the entire seqence on the server for the sake of transaction processing, create another procedure to control the sequence of events and manage the transaction. This procedure would call the others in turn and handle any errors.