Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get Data From Stored Procedure Using Sqlsrv Driver

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

like image 661
Jignesh.Raj Avatar asked Sep 02 '13 06:09

Jignesh.Raj


People also ask

How do I connect to a stored procedure?

In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.

How do I view a stored procedure?

Using SQL Server Management StudioExpand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To. Select New Query Editor Window. This will display the procedure definition.

Can we pass list in stored procedure?

Depending on the programming language and API, you can pass the list in as a table valued parameter (TVP). Other solutions will vary depending on your SQL Server version.


1 Answers

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();
}
like image 161
mcryan Avatar answered Sep 30 '22 19:09

mcryan