PHP SQL Server, call a stored procedures with parameters

Question

i'm facing this next problem:

i made a stored procedure as follow:

CREATE PROCEDURE verify_user_connection
@username nvarchar(50),
@pass nvarchar(50)
AS

BEGIN

SELECT users.username, users.pass 
FROM dbo.users
WHERE users.username = '@username'
AND users.pass = '@pass'

RETURN '@username'

END

and from the PHP code i call him like this:

  <?php
  /* Handle form buttons. */
  if (isset($_POST['login'])) {
    if (!empty($_POST['username']) || !empty($_POST['pass'])) {
      $username = $_POST['username'];
      $password = $_POST['pass'];
      $sql = "{ CALL dbo.verify_user_connection (@username = ?, @pass = ?) }";
      $param = array($username, $password);
      $result = sqlsrv_query($conn, $sql, $param);
      $row = sqlsrv_fetch_object($result);
      echo $row;
      if (!$row) {
        die(print_r(sqlsrv_errors()));
      }
      else {
        echo "record found";
      }
    }
    else {
      echo "something went wrong";
    }
  }
  ?>

well i'm kinda new in SQLSRV driver of microsoft and i know my syntax is not accurate here could someone inlight my problem and explain to me what am i doing wrong??

thanks in advance

I don't know php at all, but on your sp you should remove the ' of your variables: users.username = @username AND users.pass = @pass RETURN @username — Lamak
– Lamak, Commented Jun 18, 2012 at 15:20

Brian · Accepted Answer · 2012-06-18 15:23:18Z

1

This is'nt going to be much help, but the most reliable mthod for using SQL Server and SP's that I've found is by using PHP ADODB ( http://adodb.sourceforge.net/#download ) to connect and call procs.

I have always found the in-built sql server functions patchy at best when working with SQL server.

If you are still struggling I am happy to give you more info via email.

answered Jun 18, 2012 at 15:23

Brian

8,6212 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fedesc Over a year ago

thanks for the answer but i'll try to avoid using external plugins for simple actions , i believe that microsoft has thought about SP when they made their driver,

Brian Over a year ago

It's not the driver it's the php functions. OK... I have been working with SQL Server and PHP for many years and this is the only reliable solution.

Collectives™ on Stack Overflow

PHP SQL Server, call a stored procedures with parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related