Get Data From Stored Procedure Using Sqlsrv Driver

Question

I am using SQL server2008 as database and I have written stored procedure in MSSQL Server 2008. It's working fine in MSSQL Server 2008. I want to call this Stored Procedure from codeigniter. For that I wrote the code like this :

phpService.php:

public function Login($username, $password)
{
    $this->load->model('Apimodel');
    $result = $this->Apimodel->Login($username,$password);

    header('Content-type: application/json');
    echo json_encode(array('LoginResponce'=>$result));
}

apimodel.php:

function Login($UserName,$Password)
{               
    $this->db = $this->GetDB();
    $query =  $this->db->query("EXEC Login");

    return $query->result();

}

when I Execute Procedure without parameter it Working Fine

function Login($UserName,$Password)
    {               
        $this->db = $this->GetDB();
        $query =  $this->db->query("EXEC Login '$UserName','$Password'");

        return $query->result();

    }

But, When I Execute Procedure with parameter it's not working

Can anyone tell me What am I missing here ?

Thanks in advance

Answer 1

First of all, if you are using Codeigniter, I recommend use their database class to connect to MSSQL Server. You can read more on this here: http://ellislab.com/codeigniter/user-guide/database/configuration.html

If you aren't auto-connecting to your database you can connect like this: $this->load->database('default');

Once you have your configuration setup, you can have a function like this in your model:

function login($username, $password) {
    return $this->db->query("EXEC spLogin '$username', '$password'")->result();
}