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

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: 

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.

Not sure if it is an option for you but: I would change the SQL statement into a stored procedure, and include the return of the identity. Once a stored procedure make these changes to the statement

'SELECT SCOPE_IDENTITY() '

to

'SELECT SCOPE_IDENTITY() as id'

Then within PHP you should be able to access the id as a datatable. Stored procedures will return data as a datatable, not sure on the PHP side, I'm a .NET person

If you need help with convert the statement into a stored proc, let me know.

EDIT:
Assuming that sAMAccountName is the primary key on this table, and that what you state in your question that the you can't get the identity back during the INSERT only. Then changing the SQL to this will do the trick for you.

$sql = ''
    . 'IF (SELECT COUNT(*) FROM users WHERE sAMAccountName = ?) = 0 '
    . 'BEGIN '
    . 'INSERT INTO users (role, sAMAccountName, fullName) '
    . 'VALUES (1, ?, ?) '
    . 'END '
    . 'SELECT id FROM users WHERE sAMAccountName = ?';

You could try this out. Strip the extra single quotes and concatenations out and apply Dbloch's suggestion.

$sql = 'IF (SELECT COUNT(*) FROM users WHERE sAMAccountName = ?) = 0
            BEGIN
            INSERT INTO users (role, sAMAccountName, fullName)
            VALUES (1, ?, ?) 
            SELECT SCOPE_IDENTITY() as id
            END
            ELSE SELECT id FROM users WHERE sAMAccountName = ?';