Smarty templates with SQL and named parameters (untested pseudocode)

1-Example.tpl

<script type="text/javascript">
var data={crmSQL ext="org.civicrm.mymodule" name="my-query" prefix_id=123 contact_type="Individual"};
...
</script>

2-mymodule-my-query.mysql.tpl

{sqlVar name=prefix_id type=integer}
{sqlVar name=contact_type type=string}
{sqlQuery}
SELECT count(*)  
FROM civicrm_contact
WHERE prefix_id = {$prefix_id} AND contact_type = {$contact_type}
{/sqlQuery}

3-function.crmSQL.php

<?php

function smarty_function_crmSQL($params, &$smarty) {
  $queryFile = CRM_Core_Resources::singleton()
    ->getPath($params['ext'], 'queries/' . $params['name'] . '.mysql.tpl');
  $query = new CRM_Utils_SQL_StructuredQueryTemplate($queryFile);
  $sql = $query->toSQL($params);
  CRM_Core_DAO::executeQuery($sql);
  ...
}

4-StructuredQueryTemplate.php

<?php

class CRM_Utils_SQL_StructuredQueryTemplate {
  private $path;

  function __construct($path) {
   $this->path = $path;
   $this->smarty = CRM_Core_Smarty::singleton(); //  or maybe new/isolated instance?
  }

  /**
   * Evaluate each {sqlVar} expression in the template.
   * Results are accumulated in a Smarty variable
   * named "sqlVars" and then returned.
   * 
   * @return array (string $name => string $type) 
   */
  function getMetadata() {
   // FIXME: We want to temporarily set some Smarty vars. We do this by assign()ing and then unset()ing,
   // but it may be better to create some variant on {crmScope}.
   $this->smarty->assign('sqlMode', 'metadata');
   $this->smarty->assign('sqlVars', array());

   $this->smarty->fetch($this->path); // side-effect: {sqlVar} adds each var to 'sqlVars'
   $sqlVars = $this->smarty->get_template_vars()['sqlVars'];

   $this->smarty->unset('sqlMode');
   $this->smarty->unset('sqlVars');

   return $sqlVars;
  }
  
  /**
   * Evaluate the {sqlQuery} section of the template.
   * Results are accumulated in a Smarty variable 
   * named "sqlQuery" and then returned.
   * 
   * @return string SQL
   */
  function toSQL($params) {
    $sqlVars = $this->getMetadata();

    // FIXME: We want to temporarily set some Smarty vars. We do this by assign()ing and then unset()ing,
    // but it may be better to create some variant on {crmScope}.
   
    $this->smarty->assign('sqlMode', 'query');
    $this->smarty->assign('sqlQuery', '');

    foreach ($sqlVars as $name => $type) {
      $this->smarty->assign($name, CRM_Utils_Type::escape($params[$name], $type));
    }
    $this->smarty->fetch($this->path); // side-effect: {sqlQuery} adds evaluated query-text to 'sql'
    $sql = $this->get_template_vars()['sqlQuery'];
 
    $this->smarty->unset('sqlMode');
    $this->smarty->unset('sqlQuery');
  
    return $sql;
  }
}