Code reuse and modularity in SQL

Question

Is code reuse and modulatiry a good idea for SQL Stored Procedures programming?

And if so, what's the best way to add these features to a SQL stored procedure code base?

I usually create scalar valued functions for tasks that are common and repeated. I find that it eases both development of new procedures similar to existing ones, but also aids a lot in bugtracking and troubleshooting.

I try to stay away from table valued functions though, due to performance issues.

My rule of thumb is that if it is a calculation, and it's used in several places, then I create a scalar valued function.

Answer 1

Sql doesn't give you a lot of flexibility when it comes to code reuse. I usually create functions when it comes to calculations or other tasks that don't involve modifying tables. But all tasks that involve writing to tables and that sort of things I usually use a stored procedure to get a better control of the transactions.

Answer 2

Another way to look at it from the applications side is to use binding to reuse your SQL queries. But that's probably not what your looking for

Answer 3

You can break code into separate stored procedures to help break down complex stored procs into more manageable chunks. You can also do the same to break out common logic that won't work in a function. Think of it similar to an Extract Method refactoring.

Answer 4

You are going to find that using functions within your queries is a disaster for performance. The functions become a black box for the optimizer, so you will end up re-coding the function call back into the query to make it run fast once you get up to a large number of rows in your tables.

A better way to deal with common calculations is to insert them into a new column with a trigger, or in your insert/update queries. That way you can index the calculated value and use it directly instead of figuring it out each time you need it.

Answer 5

To follow up on this, I did run into some performance problems, and it seems that the optimizer is not able to pick the correct index for code inside the functions.

So I had to specify the correct index using index hints (with keyword), to solve the performance issue.