CODEWITHSUNDEEP
php
Aly
Aly

Reputation: 11

Calling a MS SQL stored Procedure From PHP

i have a stored procedure running in MSSQL 2008 R2, i am using PHP 5.3, i can connect successfully to the db and retrieve data, but i have been trying to call a stored procedure and haven't succeeded, i need to pass parameters to the stored procedure then get the results back here is my code but its not executing successfully. anyone please with an example on how i can do this.

<?PHP
  $sql = " { call rpt_TOP_PRODUCTS } ";//my stored procedure
  $param1 = 10;
  $param2 = 'E';
  $param3 = 'SZ';
     $params = Array(Array($param1,$param2,$param3, SQLSRV_PARAM_IN)//parameters to be     passed  );
    $stmt = sqlsrv_query($conn,$sql,$params);

      if ($stmt===false) {
       // handle error
   echo 'Success No';//THIS IS WHERE ITS GOING WHEN I RUN THE CODE
       print_r(sqlsrv_errors,true);
      } else {
         if (sqlsrv_execute($stmt)===false) {
        // handle error.  This is where the error happens
         print_r(sqlsrv_errors,true);
     echo 'Success Not ';
      } else {


  echo 'Success True Yeah';//THIS IS WHERE I WANT IT TO COME.
    }
  }
<?

Upvotes: 0

Views: 4536

Answers (4)

Maxi
Maxi

Reputation: 743

Try this

<?php
 $sql = " { call rpt_TOP_PRODUCTS} ";//my stored procedure
$param1 = 10;
  $param2 = 'E';
  $param3 = 'SZ';
$params = array($param1,$param2,$param3);//parameters to be     passed  );
    $stmt = sqlsrv_prepare($conn,$sql,$params)or die(print_r(sqlsrv_errors(),true));

      if ($stmt===false) {
       // handle error
   echo 'Success No';//THIS IS WHERE ITS GOING WHEN I RUN THE CODE
       print_r(sqlsrv_errors(),true);
      } else {
         if (sqlsrv_execute($stmt)===false) {
        // handle error.  This is where the error happens
         print_r(sqlsrv_errors(),true);
     echo 'Success Not ';
      } else {


  echo 'Success True Yeah';//THIS IS WHERE I WANT IT TO COME.
    }
  }?>

If your procedure is not there, you will get an error saying the procedure could not be found

Upvotes: 0

Devin Young
Devin Young

Reputation: 841

Better late than never. Marc B is right about your print_r functions. Also, with the sqlsrv PHP functions, you have to prepare a query with "sqlsrv_prepare()" before you can execute it. Just change:

$stmt = sqlsrv_query($conn,$sql,$params)

to:

$stmt = sqlsrv_prepare($conn,$sql,$params)

And it should run just fine.

Upvotes: 0

ShiftyThomas
ShiftyThomas

Reputation: 476

You need to define where your params are in the query 

    call rpt_TOP_PRODUCTS(?,?)

Upvotes: 0

Marc B
Marc B

Reputation: 360882

Your print_r() calls are wrong, they should be

print_r(sqlsrv_errors(), false);
                     ^^---missing in yours

Without the (), PHP sees that as an undefined constant and outputs nothing. With the (), it's a function call that'll return the error messages from your DB call.

Try rerunning your code with that and see what the errors are.

Upvotes: 1

Related Questions