从PHP页面调用SQL Server存储过程，并接收字符串输出参数

Question

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.

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.

Here is my stored proc:

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 > 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

Here is my PHP page (check_code.php):

<?php
    $crcode = "";
    $sponsor = "";

    $serverName = "server_name\db_name, 31433";
    $connectionInfo = array( "Database"=>"db_name", "UID"=>"username", "PWD"=>"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);
?>

Note: the variable $crcode is hard-coded just for testing purposes

When I load the PHP page in my browser, I get the message:

Error encountered, please contact support

I have tested the stored proc in MSSQL Management Studio, and it works fine.

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:

    $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";
    }

But that just produced the following error:

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

Any thoughts on how I can get the string back from the stored proc?

Answer 1

So I never figured out how to solve the problem with a stored procedure (and if anyone feels inclined to provide guidance on how to do that, I would be most grateful); but I did solve the problem using a slightly different approach. Here is my PHP code:

<?php
    function check_sponsor_code() {
        //$sponsor_code = $_GET['crcode'];
        $sponsor_code = "test_cr_code";
        $sponsor = "";

        $serverName = "server\dbname, 31433";
        $connectionInfo = array( "Database"=>"dbname", "UID"=>"username", "PWD"=>"password");
        $conn = sqlsrv_connect( $serverName, $connectionInfo);


        $query = "SELECT 
                    p.fname + ' ' + p.lname + ' (' + c.name2 + ')' AS sponsor
                FROM 
                    person_table p INNER JOIN
                    company_table c ON p.company_id = c.id
                WHERE 
                    p.custom5 = 'Yes'
                AND p.custom0 = ?";

        if(! $conn ){
            die('Could not connect: ');
        }

        $params = array($sponsor_code);

        $stmt = sqlsrv_query($conn, $query, $params, array( "Scrollable" => 'static' ));

        if ($stmt) {
            $row_count = sqlsrv_num_rows($stmt);
            //echo "Row count: ".$row_count."<br/>";
            if ($row_count == 0) {
                $sponsor = "Error (Unknown Code) - the Sponsor Code provided is invalid. Please check that you have entered the correct code.";
            } elseif ($row_count > 1) {
                $sponsor = "Error (Multiple Sponsors) - the Sponsor Code provided is invalid. Please contact the System Administrator.";
            } elseif ($row_count == 1){
                while($row = sqlsrv_fetch_array($stmt)){
                    $sponsor = "Success! Your Headway DNA Sponsor is: ".($row['sponsor']);
                }
            } else {
                $sponsor = "An unknown error occurred.";
            }
        } else {
            echo "Error verifying record: Invalid Sponsor Code...<br/>";
        }
        sqlsrv_close($conn);

        echo $sponsor;
    }

    check_sponsor_code();
?>

It works nicely, so unless someone tells me it's not a good solution I'm going to run with it.