Working With Stored Procedures in Drupal can be a bit tricky. Here is a quick example of how to do it.
StoredProcedureController.php
// We don't use execute(), we directly run fetchAssoc() to get an associative array or fetchObject() to get a an object
$sp = Database::getConnection()->query("EXECUTE DRU.SP_NAME", $options)->fetchAssoc();
// $sp = Database::getConnection()->query("EXECUTE DRU.SP_NAME", $options)->fetchObject();
// Return it via the render array
return [
'#sp' => $sp,
]
We need to use hook_theme() to tell Drupal about the twig file and the variable we're passing to it.
StoredProcedure.module
function HOOK_theme(){
return [
'variables' => [
'#sp' => $sp,
],
];
}
StoredProcedure.html.twig
// The data is passed from the controller to the twig file via the render array
// Since it's an associative array, we need the key and value
{% for key,value in sp %}
Key :
{{ key }}
Value :
{{ value }}
{% endfor %}