Menu
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.
610
Modularization is the process by which you break up large blocks of code into smaller pieces (modules) that can be called by other modules. Modularization of code is analogous to normalization of data, with many of the same benefits and a few additional advantages. With modularization, your code becomes: More reusable.
Reusable: As mentioned, multiple users and applications can easily use and reuse stored procedures by merely calling it. Easy to modify: You can quickly change the statements in a stored procedure as and when you want to, with the help of the ALTER TABLE command.
What is a Stored Procedure? A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
The HAVING clause is used to filter data from groups according to the specified condition. The WHERE clause is used to filter individual content from table according to the specified condition. HAVING clause is used after the groups are made (Post-filter). WHERE clause is used before the groups are made (Pre-filter).
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.
90
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.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
