Using extract method on stored procedure

Question

Extract method is a common refactoring pattern when writing programming languages.

When I try to do some refactorings on my stored procedures, I am wondering if it is also a good practice to use extract method when writing stored procedures (SP)/User-defined functions (UDF) since we can call other SPs/UDFs on a SP/UDF?

Does it affect performance?

Thanks in advance.

Answer 1

Just my opinion (working for several years with databases now):

Stored procedures should be used for database tasks only. For example migrating data (currently I'm working on a process to transform a database structure for example), or some dynamic queries (where a sql statement is built on the fly), or maybe a procedure to build a table (for example a table that holds dates for a specific date range).

Not for anything else! For everything that gets more complicated than above examples consider to code it on application layer.

Also, you maybe heard that it's wise to put as much business logic into the database as possible. That's true for the database design, but it does not mean, that you should code almost everything in it. Databases are not good at that (talking for example about data transformation or something like that). A programming language like PHP or whatever is faster!

So, for everything that I used stored procedures for, I never felt the need to put anything in extra procedures. Apart from for example the restructuring of a database (in my case it's a ETL process (it denormalizes data into a star schema for better performance)), there I wrote a procedure for every table and these procedures are called from a procedure that manages the whole process. But again, it's nothing like a programming language.

Also, when I take this example for extract method pattern http://www.refactoring.com/catalog/extractMethod.html
having something like this in your database will become a debugging nightmare and you will spend way too much time coding. And again, the cases where a stored procedure should be used are not cases where it makes sense to apply the extract method pattern.