Avoiding duplicating SQL code?

Question

I know many ways to avoid duplicating PHP code (in my case PHP). However, I am developing a rather big application that does some calculations on the database with the data it finds, and I have noticed the need to use the same code (parts of SQL) in other places.

I don't like the idea of copying and pasting the same thing over and over again. What is a good way to do this? Should I use stored procedures? I could almost calculate some of the stuff in PHP except that most of the times the queries are calculating values based on also data not returned by the query and it seems stupid to return extra data to PHP so that it could its calculations. Sometimes that may be okay, but now it does not feel so.

What should I do?

For example, all over in many SQL queries I am calculating similar to this:

...
(SELECT SUM(amount) FROM IT INNER JOIN Invoice I WHERE IT.invoiceId=I.id) AS total
...
FROM InvoiceTransaction IT
...

Note that I'm at home now so I'm writing this off the top of my head.

Which database? I'm assuming it's MySQL but you never know... — Mark Byers
– Mark Byers, Commented Sep 18, 2010 at 8:19
Is there a table called IT? If not, does the query actually work? — Thomas Mueller
– Thomas Mueller, Commented Sep 18, 2010 at 10:59

Brian Agnew · Accepted Answer · 2010-09-18 08:28:20Z

2

I think you have 2 solutions:

if the SQL returns a small amount of data, I would simply wrap the SQL invocation in a method call and call it (parameterising as necessary)
if the SQL handles a lot of data, I would keep that data in the database and use a stored procedure. You can then call that stored procedure without duplicating the code (but wrap the stored proc call in a function and call it - i.e. as in option 1)

I wouldn't necessarily shy away from stored procedures. But I would advise keeping business logic out of them (keep it in the application itself) and make sure you have sufficient unit testing around it.

answered Sep 18, 2010 at 8:28

Brian Agnew

273k38 gold badges342 silver badges443 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tia · Accepted Answer · 2010-09-18 08:22:11Z

1

I do not prefer store procedure, especially not for the sake of refactoring. You should consider writing a function that return the record you need, and put your SQL queries in that function so you can call it instead of putting your SQL everywhere.

answered Sep 18, 2010 at 8:22

tia

9,7481 gold badge33 silver badges47 bronze badges

Comments

Adam Butler · Accepted Answer · 2010-09-18 08:31:12Z

1

I think we would need an example of a query. Stored procs might be a good option. Or an alternative might be to use views. One advantage in having your queries in views or stored procs is that you can often use the database to see where your tables are used. Disadvantage is that you are locking yourself into one database, however you are probably doing this anyway.

answered Sep 18, 2010 at 8:31

Adam Butler

3,0475 gold badges37 silver badges41 bronze badges

Collectives™ on Stack Overflow

Avoiding duplicating SQL code?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related