pagination of SqlDataProvider not work me when use the call of a stored procededure

Question

I'm using the SQLDataProvider to call a procedure that returns a list of records but I can't get it to work

what i'm trying is:

 $dataProvider = new SqlDataProvider([
            'sql' => "CALL ErroresEnHoras(:project_id, :fecha_iniciop, :fecha_finp, :proyecto)",
            'params' =>
            [
                ':project_id' => ($this->proyectoid == '' || is_null($this->proyectoid)) ? 0 : $this->proyectoid,
                ':fecha_iniciop' => ($filtrosModel->fecha_inicio == '' ? null : $filtrosModel->fecha_inicio ),
                ':fecha_finp' => ($filtrosModel->fecha_fin == '' ? null : $filtrosModel->fecha_fin ),
                ':proyecto' => ($filtrosModel->proyecto == '' ? null : $filtrosModel->proyecto)
            ],
            'totalCount' => 335,
            'pagination' => [
                'pageSize' => 10,
            ],
        ]);

the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 1 The SQL being executed was: CALL ErroresEnHoras(0, NULL, NULL, NULL) LIMIT 10

I know why I get the error, because the query is going wrong. Is there another way to do it?

Thank you very much in advance

Answer 1

Your mistake is, that stored procedures don't return anything really, while stored functions return only one value. So there are not direct solution, not even with ArrayDataProvider.

The workaround is to use an intermediate table for the communication. You could pass a unique query ID to your procedure and you insert the result in the body of the procedure into intermediate table with the query ID.

Use the data provider on the intermediate table. After the processing is ready you can remove the records with the query ID from the intermediate table.