title: Call SQL Server stored proc from PHP page, and receive string output parameter
body: <p>I am trying to call an SQL Server stored procedure from a PHP page, and have the stored proc return a string value in an output parameter.</p>
  
  <p>I already have a number of PHP pages which successfully call SQL Server stored procedures, and the procedures return an integer value. I also have a PHP page which executes a simple SELECT statement and gets back a string value.  But now I have a stored proc which accepts an INPUT parameter from the PHP page, then it selects some data from a couple of tables, and creates a completely new string by concatenating it with some static text.  I want to return that concatenated string to the calling PHP page.</p>
  
  <p>Here is my stored proc:</p>
  
  <pre><code>CREATE PROCEDURE CHECK_SPONSOR_CODE
      @cr_code            VARCHAR(50),
      @sponsor            VARCHAR(255) OUTPUT
  AS
      DECLARE @xrowcount      INT;
  BEGIN
      SET NOCOUNT ON;
  
      BEGIN
          SELECT @xrowcount = count(custom0)
          FROM person_table
          WHERE custom0 = @cr_code 
          AND   custom5 = 'Yes';
  
          IF (@xrowcount = 0) 
          BEGIN           
              SELECT @sponsor = 'Error (Unknown Code) - the Sponsor Code provided is invalid. Please check that you have entered the correct code.';
          END
  
          IF (@xrowcount &gt; 1) 
          BEGIN           
              SELECT @sponsor = 'Error (Multiple Sponsors) - the Sponsor Code provided is invalid. Please contact the System Administrator.';
          END
  
          IF (@xrowcount = 1) 
          BEGIN           
              SELECT 
                  @sponsor = 'Success! Your Sponsor is: ' + p.fname + ' ' + p.lname + ' (' + c.name + ')'
              FROM 
                  person_table p INNER JOIN
                  company_table c ON p.company_id = c.id
              WHERE 
                  p.custom0 = @cr_code 
              AND p.custom5 = 'Yes';
          END 
      END
  END
  </code></pre>
  
  <p>Here is my PHP page (check_code.php):</p>
  
  <pre><code>&lt;?php
      $crcode = "";
      $sponsor = "";
  
      $serverName = "server_name\db_name, 31433";
      $connectionInfo = array( "Database"=&gt;"db_name", "UID"=&gt;"username", "PWD"=&gt;"password");
      $conn = sqlsrv_connect( $serverName, $connectionInfo);
  
      $query = "{call CHECK_SPONSOR_CODE (?, ?)}" ;
  
      $crcode = "test_cr_code";
  
      if(! $conn ){
          die('Could not connect: ');
      }
  
      $params = array(
          array($crcode, SQLSRV_PARAM_IN),
          array($sponsor, SQLSRV_PARAM_INOUT)
          );
  
      $stmt = sqlsrv_query($conn, $query, $params);
  
      if ($stmt) {
          sqlsrv_next_result($stmt);
          echo $sponsor;
      } else {
          echo "Error encountered, please contact support";
      }
      sqlsrv_close($conn);
  ?&gt;
  </code></pre>
  
  <p>Note: the variable $crcode is hard-coded just for testing purposes</p>
  
  <p>When I load the PHP page in my browser, I get the message:</p>
  
  <p><strong>Error encountered, please contact support</strong></p>
  
  <p>I have tested the stored proc in MSSQL Management Studio, and it works fine.</p>
  
  <p>I think the problem might be something to do with the procedure returning a result set to the PHP page, and the last thing it returns is the OUTPUT parameter.  And somehow that is causing the PHP page to get upset (sorry for getting technical).  I tried modifying it as follows:</p>
  
  <pre><code>    $params = array(
          array($crcode, SQLSRV_PARAM_IN),
          array($sponsor, SQLSRV_PARAM_INOUT)
          );
  
      $stmt = sqlsrv_query($conn, $query, $params);
      $row_count = sqlsrv_num_rows( $stmt );
  
      if ($stmt) {
          sqlsrv_next_result($stmt);
          echo $sponsor;
      } else {
          echo "Error encountered, please contact support";
      }
  </code></pre>
  
  <p>But that just produced the following error:</p>
  
  <p><strong>Warning: sqlsrv_num_rows() expects parameter 1 to be resource, boolean given in D:\Apache2.2\htdocs\check_code2.php on line 23
  Error encountered, please contact support</strong></p>
  
  <p>Any thoughts on how I can get the string back from the stored proc?</p>