Is it a bad practice to use external files for storing SQL templates?

Question

I am working on a report system which aggregate a lot of data from multiple sources (databases and internal APIs). Among other things I communicated with another departaments and told them I need some aggregated data from their database, so they just gave me pretty huge SQL query (about 100 lines, 15 joins etc) which work fast and do what I need. There is a couple of places in that query where i need to replace something based on conditions.

So my question is:

What is the best way to handle it?

Is it normal to store it in external file and process it just like HTML templates?

• JeffO: How do you manage code for all the other databases you have to query?
• Nikita U.: In other cases there are views and special tables with aggregated data, so queries to them is very simple. In case that is subject of my question I can't affect the database and forced to use just huge SQL query

Answer 1

This might depend on the database system in question, but usually such parameterized query logic would be in a stored procedure or view on the database. If multiple clients are performing similar queries in the same database using their own "sql template", you have code duplication, which becomes a problem if a bug is found or if the database schema have to modified. So I wouldn't say it is a good practice in general.