Run Stored Procedure From Page Load

Question

I have a html table that has a hyperlink in it, when the hyperlink is pressed 3 parameters are passed to a secondary page. The query that is run is a CTE SQL Server statement, and from my understanding the wonderful app sourcerer can not handle these type statements, so I am using a stored procedure.

What is the proper way to run a SQL Server Stored Procedure from page load in a Joomla Article? I know how to run the stored procedure from the article, just not sure of how to have it execute from the page load. Ideally have the hyperlink be a 2 step process
1) Run Stored Procedure
2) redirect
However if that is not possible, how would I run the stored procedure from page load?

(The procedure must take the parameters that are passed which is why I can not have it pre-load)

Answer 1

Sourcerer can in fact run a CTE query. Just set it up as a long query string as if you were going to run inline sql in SSMS. Like this:

//Database Connection Info
$db = JDatabase::getInstance( $option );
$query = $db->getQuery(true);
$query = ";WITH CTE AS ..... rest of query";
$db->setQuery($query); 
$query = $db->loadObjectList();
//Spit out results however you need