Retrieving data from a stored procedure using the Sqlsrv driver

Question

I use SQL server2008 as a database, and I wrote a stored procedure in MSSQL Server 2008 . It works great in MSSQL Server 2008 . I want to call this stored procedure from codeigniter . To do this, I wrote the code as follows:

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 a procedure without a parameter Works fine

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

But when I execute the procedure with the parameter, it does not work

Can anyone tell me what I'm missing here?

Thank you in advance

Answer 1

oh .............. after a long RND I got ans

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 $username,$password"); return $query->result(); } 

Answer 2

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

If you are not connecting to your database, you can connect as follows: $this->load->database('default');

After configuring, you can have the same function in your model:

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