Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlU
Starting Member
4 Posts |
 Posted - 2009-03-03 : 00:34:54
|
| I am looking for some general advice on refactoring very large stored procedures. I am working with a few from an inherited database. They makes heavy use of temp tables to perform a series of dependent updates, deletes, etc. Performance issues aside, larger procedures tend to be more difficult to follow than a series of smaller ones. I have considered breaking them into smaller procs, but with the usage of temporary tables I am not sure how best to do that. I know there are variables, but I would be interested in hearing thoughts, tips or opinions on how to approach this situation. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-03 : 09:38:56
|
| i think if there are similar types of logic which is used at quite lot of procedure, then you can make a table valued udf to include logic and call it from all the procedures. by that way, you can remove redundant code and also make them replace temporary tables. |
 |
|
|
sqlU
Starting Member
4 Posts |
Posted - 2009-03-03 : 12:04:34
|
| Yes, that is a good thought. Unfortunately, I am not sure a table udf would work here. A lot of the code is dependent on updates/deletes done in previous steps. If I were to use a table udf, I don't think I could maintain those changes in between stored procedure calls. That is probably why they used temp tables originally (ie because they needed to maintain the changes ) |
|
|
|
|
|
|
Refactoring large stored procedures