SQL Server Performance Forum – Threads Archive

Calling stored procedures from stored procedure

Hello everyone I am developing a master stored procdure which contains cursor. This cursor fetches records from CompanyMaster, and for each record in company master i want to run certain other stored procdures. These stored procedures are some businiess rules. So in due curse of time they may change, or the companymay want to add some more. If there is a change in the rule then we will just have to modify the respective stored procedure. but if the client wants to add new rule then we will have to first develop a stored procedure for that rule. and modify the master stored procedure. So as to achive this, is there any other alternative for this process. What i was thinking is storeing the stores procedure names in table. and whenever we want to call any of them we will just refer that name and call the stored procedure. these stores procedures have only one parameter that is CompanyID. Can this be done? Pls do let me know if u think of any other option. thanks in advance Pallavi

You can replace cursor with the While Loop.
1 for looping through the CompanyTable &
2 for looping through the SPs Name Table.
Regards,
Mayur.

This way you force row by row processing. I hope you are aware of performance penalty you pay for such general design.

I don’t think these answers are to the point, this is not about cursor versus set-based … The idea of using a WHILE loop is overkill, assuming that there are no frequent changes to the list of procedures. As long as all procedures have the same parameter, the master procedure should work fine. You should build in a check that the procedure exists, and fail if it doesn’t. You should however monitor the time that the master procedure takes to finish, and also of the individual procedures. … but anyway I don’t see how this would be preferable to changing the master procedure! If there are frequent changes, then perhaps the system requirements are not yet finalized. In that case you really shouldn’t be this far into building the actual system.

Create a table and insert procedure name, parameter , priority or execution order, status etc. Now write one procedure that will loop thru this table based on status, and priority. In future if you want to add more procedure you can do so by simply inserting a record here and you don’t have to modify the main calling procedure. If you want to retire any procedure then simply change the status to inactive. I have successfully used this approach because we have 70 clients and they all have some custom code in many of such procedures. So those clients have their version of procedures in this lookup table and my main procedure simply loops thru it.
In my case the main proc was called by only one person as it was for claim processing. You should think on those issues if multiple people are executing this proc. You may want to evaluate row by row or set based approach for perf reasons. Meaning either each procedure can process all the records in company master or they can process single record in company master. This depends on your business rule as well because of dependencies.

Thanks to all Actually i was havin problem in calling te stored procedures, Thanks indshri for solving my problem. This was ecactly what i wanted thanks a lot. Pallavi

]]>