I have a somewhat large query (~8 tables being joined, 100 lines of sql or so) that I use frequently in order to join many different data sources together. I would like to be able to pass a parameter into the query so that myself and other members of my team can work off the same code base and just change the time period that we are looking at.
Example code (obviously my actual code is more involved than this, but these are some of the things that I need to do):
SELECT t1.* , x.col1, x.SUM_col3
FROM table1 t1
LEFT JOIN
(
SELECT t2.col1, t3.col2, SUM(t3.col3) as SUM_col3
FROM table2 t2
INNER JOIN table3 t3
ON t2.PI = t3.SI
WHERE t3.col2 NOT LIKE :parameter1
GROUP BY 1,2
QUALIFY ROW_NUMBER() OVER(PARTITION BY t2.col1 ORDER BY t3.col1) = 1
) x
ON t1.col1 = x.col1
WHERE t1.START_DATE >= :parameter2
Solutions I have considered:
- Using the '?' prefix in order to enter the parameters at run time. I find this method to be quite inefficient as the entered parameter is retained in the code after running the code.
- Using Dynamic SQL to create a procedure which can then be called. I'm struggling to get this method to work as its intended purpose seems to be relatively short queries.
How can I best structure my query and code in order to be able to run something like CALL MY_QUERY(:parameter1,:parameter2) in such a way that it either creates the resulting table in memory (less preferred) or returns a result set that I can then store or use myself (more preferred).