CodeIgniter Forums
calling a stored procedure with sqlsrv vs mssql driver needs "EXEC " prefix - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forum-6.html)
+--- Forum: Issues (https://forum.codeigniter.com/forum-19.html)
+--- Thread: calling a stored procedure with sqlsrv vs mssql driver needs "EXEC " prefix (/thread-70828.html)



calling a stored procedure with sqlsrv vs mssql driver needs "EXEC " prefix - Lxocram - 06-05-2018

Just posting this here for people scratching their head when moving from mssql driver to sqlsrv driver.

if you are calling a stored procedure e.g.

$this->db->query('get_data @parameter=?', $parameter)

this works under mssql driver
when using sql driver you should change it to

$this->db->query('EXEC get_data @parameter=?', $parameter)

otherwise you get num_rows=-1 and no results


RE: calling a stored procedure with sqlsrv vs mssql driver needs "EXEC " prefix - SomeGuy - 06-09-2018

Seems like it would be better practice to extend the db driver to standardize the calling of stored procs.

Reference: https://forum.codeigniter.com/thread-68512-post-345512.html#pid345512

File: application/libraries/MY_DB_mssql_driver.php
PHP Code:
class MY_DB_mssql_driver extends CI_DB_mssql_driver {
    public function 
stored_proc($param) {
        return 
$this->query('get_data @parameter=?'$param);
    }


File: application/libraries/MY_DB_sqlsrv_driver.php
PHP Code:
class MY_DB_sqlsrv_driver extends CI_DB_sqlsrv_driver {
    public function 
stored_proc($param) {
        return 
$this->query('EXEC get_data @parameter=?'$param);
    }