如何在PHP中使用Microsoft SQL驱动程序选择SCOPE_IDENTITY（）？

Question

I have a special MSSQL statement which I use to retrieve the identity of a specified row that either already exists or else INSERTs and then retrieves that identity. This SQL appears to work great in SQL Management Studio as well as a couple of my .NET C# apps.

Unfortunately, I've been asked to use PHP for this particular task and I am having great difficulty trying to use PHP and the official Microsoft SQL driver.

The statement isn't returning an ID after the INSERT (or if it is, I'm not able to retrieve it). Could somebody please see where and if I'm going wrong?

I'm using PHP 5.3.23 with Microsoft SQL Driver 3.0. Here is my code:

$sql = ''
        . 'IF (SELECT COUNT(*) FROM users WHERE sAMAccountName = ?) = 0 '
        . 'BEGIN '
        . 'INSERT INTO users (role, sAMAccountName, fullName) '
        . 'VALUES (1, ?, ?) '
        . 'SELECT SCOPE_IDENTITY() '
        . 'END '
        . 'ELSE SELECT id FROM users WHERE sAMAccountName = ?';
$params = array($requester, $requester, $fullName, $requester);
$query = $this->SqlQuery($sql, $params);

function SqlQuery($sql, array $params) {
    $conn = $this->SqlConnection(null, null, null, null); // x4 null = use default connection options
    $query = sqlsrv_query($conn, $sql, $params);
    if ($query) {
        sqlsrv_fetch($query);
        echo 'Result: ' . sqlsrv_get_field($query, 0); // This should return an ID regardless
        sqlsrv_close($conn);
    }
}

Here is the result HTML (No Result):

Result: 

Answer 1

You need a SET NOCOUNT ON; at the beginning of your query.

Your INSERT statement actually returns something: an empty recordset plus the message "(1 row affected)". SSMS just concatenates all the recordsets, but in sqlsrv you have to explicitly advance to the second recordset to see your SCOPE_IDENTITY() result. By turning off the rowcount feature the uninteresting results set is quashed, and it will do what you want.