SQLSRV Query Returning Output Parameter As Strange Text

I happened to have SQL Server and PHP installed on my VirtualBox, so I played around.

Few things:

• Init parameters with the value of a proper type.
• Use & when passing parameters.
• Read all results that the stored procedure returns using sqlsrv_next_result. This advice is taken from sqlsrv_prepare in the example at the bottom.

Here is the code:

$search = "qweryt";
$return = 123;

$tsql_callSP = "EXEC Intranet.CustomerSearch @Search=?, @Return=?";
$params = array( 
    array(&$search, SQLSRV_PARAM_IN),
    array(&$return, SQLSRV_PARAM_OUT),
    );

$stmt = sqlsrv_query($conn, $tsql_callSP, $params);
if ($stmt === false)
{
    echo "EXEC Intranet.CustomerSearch Failed\n";
    die( print_r( sqlsrv_errors(), true));
}
else
{
    while($res = sqlsrv_next_result($stmt))
    {
        // make sure all result sets are stepped through, 
        // since the output params may not be set until this happens
    }
}

echo $return;

Verified with PHP 5.4.28, SQL Server Express 2014, Microsoft driver for PHP SQLSRV 3.2

When you have a stored procedure that does only INSERT or UPDATE, i.e. doesn't return result of SELECT, then sqlsrv_next_result happen to be not needed to get values of the output parameters. If there is SELECT together with output parameters, it effectively means that procedure returns several result sets and you need to read all of them.

Actually, here is a very similar question.

Edit

If I understood you correctly, you want to retrieve the value of the OUTPUT parameter first. Then you want to retrieve the result of the SELECT statement.

As far as I understand, it is not possible. SQL Server returns result of SELECT statement in the first result set, which you can read using sqlsrv_fetch_array. Then you move to the second result set using sqlsrv_next_result. At this moment the value of the OUTPUT parameter is transferred to your PHP variable, because SQL Server sends the values of OUTPUT parameters in the last result set. After you call sqlsrv_next_result the first result set is no longer available when you do sqlsrv_fetch_array.

By the way, when you said

What is strange is if I create a stored procedure that is an INSERT or UPDATE statement the OUTPUT returns correctly.

, it makes perfect sense. When stored procedure has no SELECT statements and it has SET NOCOUNT ON;, then the only result set that is returned is for OUTPUT parameters, so your PHP variable is assigned with correct value without explicit calls to sqlsrv_next_result.

So, you have at least two options.

1) Read the full result of SELECT into a temporary array using sqlsrv_fetch_array. Then use sqlsrv_next_result to get the value of the OUTPUT parameter. Then process result of the first SELECT saved in your array based on the received value of the OUTPUT parameter.

2) Do not use OUTPUT parameter at all. With such parameter(s) SQL Server returns multiple result sets implicitly. Since you have multiple result sets any way, return two result sets explicitly. In your stored procedure at first do SELECT that returns just one row with one number, then your main SELECT:

ALTER PROCEDURE [Intranet].[CustomerSearch]
    @Search nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS
        (
            SELECT KeyCode
            FROM Autopart.dbo.Customer
            WHERE KeyCode = @Search
        )
    BEGIN
        SELECT CAST(1 AS int) AS ReturnCode;

        SELECT
            Customer.KeyCode
            ,Customer.X13
            ,Customer.Name
            ,Customer.Addra
            ,Customer.Addrb
            ,Customer.Addrc
            ,Customer.Addrd
            ,Customer.Addre
            ,Customer.PCode
            ,Customer.CTitle
            ,Customer.Climit
            ,Customer.Ptype
            ,Customer.Stel
            ,Customer.SCont
            ,Customer.ACont
            ,Customer.XString5
            ,Customer.Locked
            ,Customer.Email
            ,Customer.StopStatus
            ,CusNotes.Comment
        FROM
            Autopart.dbo.Customer
            LEFT OUTER JOIN Autopart.dbo.CusNotes ON Autopart.dbo.Customer.KeyCode = Autopart.dbo.CusNotes.Account
        WHERE
            (Customer.KeyCode = @Search)
            AND (CusNotes.Seqno = 1 OR CusNotes.Seqno IS NULL)
        ;

    END ELSE BEGIN

        SELECT CAST(0 AS int) AS ReturnCode;

        SELECT TOP 100
            KeyCode
            ,Name
            ,PCode
            ,Addra
            ,Addrb
            ,Addrc
        FROM
            AUTOPART.dbo.Customer
        WHERE
            KeyCode LIKE '%'+@Search+'%'
            OR Name LIKE '%'+@Search+'%'
            OR PCode LIKE '%'+@Search+'%'
        ;
    END
END

In PHP code call sqlsrv_fetch_array first time to read ReturnCode. Then call sqlsrv_next_result to switch to the second result set. Then call sqlsrv_fetch_array to read result of the main SELECT.

$search = "qweryt";

$tsql_callSP = "EXEC Intranet.CustomerSearch @ParamSearch=?";
$params = array
    (
    array(&$search, SQLSRV_PARAM_IN)
    );

$stmt = sqlsrv_query($conn, $tsql_callSP, $params);
if( $stmt === false )
{
    echo "EXEC Failed\n";
    die( print_r( sqlsrv_errors(), true));
}

echo "First result set:\n";
while ($row = sqlsrv_fetch_array($stmt))
{
    var_dump($row);
}

echo "Next result:\n";
$next_res = sqlsrv_next_result($stmt);
var_dump($next_res);

echo "Second result set:\n";
while ($row = sqlsrv_fetch_array($stmt))
{
    var_dump($row);
}