scope_identity problem when using trigger without SET NOCOUNT ON option

[genyslt]

Hello,

Issue

The query doesn't return scope_identity if i have some operations on trigger and didn't use SET NOCOUNT option

SQL Code

CREATE TABLE Cars
(
CarId INT IDENTITY PRIMARY KEY,
Name NVARCHAR(255) NOT NULL,
Total INT NOT NULL DEFAULT 0
)

go

CREATE TABLE Cars_In
(
CarInId INT IDENTITY PRIMARY KEY,
CarId INT NOT NULL REFERENCES Cars(CarId),
Amount INT NOT NULL DEFAULT 0
)

go

CREATE TRIGGER Cars_In_I ON Cars_In
AFTER INSERT
AS
BEGIN

    --1. SET NOCOUNT IS ON - SCOPE_IDENTITY RETURNS VALUE OK
    --2. SET NOCOUNT IS COMMENTED - SCOPE_IDENTITY RETURNS FALSE

    --SET NOCOUNT ON;

    DECLARE @CarId INT

    SELECT @CarId = CarId
    FROM INSERTED

    UPDATE Cars
    SET Total = (SELECT SUM(Amount) FROM Cars_In WHERE CarId = @CarId)
    WHERE CarId = @CarId

END

go

INSERT INTO Cars
    (Name)
SELECT 'Ford'
UNION
SELECT 'Volkswagen'
UNION
SELECT 'Mercedes Benz'

go

PHP Code

$query = 'INSERT INTO Cars_In (CarId, Amount) VALUES (1,2); SELECT SCOPE_IDENTITY() AS ID';
$stmt = sqlsrv_query($conn, $query);
sqlsrv_next_result($stmt);
sqlsrv_fetch($stmt);
$scope = sqlsrv_get_field($stmt, 0);
die($scope);

Question

1. When SET NOCOUNT IS ON - SCOPE_IDENTITY RETURNS VALUE - OK
2. Try to comment SET NOCOUNT - SCOPE_IDENTITY does not return value

System info

PHP version: 7.1.5 Linux version: Ubuntu 16.04.2 LTS sqlsrv_client_info: [DriverName] => libmsodbcsql-13.1.so.8.0 [DriverODBCVer] => 03.52 [DriverVer] => 13.01.0008 [ExtensionVer] => 4.0.8.0 sqlsrv_server_info: [SQLServerVersion] => 12.00.2000

[yukiwongky]

@genyslt ,

This is because when SET NOCOUNT IS OFF, another result set is returned.

In your script, you have

$query = 'INSERT INTO Cars_In (CarId, Amount) VALUES (1,2); SELECT SCOPE_IDENTITY() AS ID';
$stmt = sqlsrv_query($conn, $query);

It looks like two queries are being done:

1. INSERT INTO Cars_In (CarId, Amount) VALUES (1,2)
2. SELECT SCOPE_IDENTITY() AS ID

But actually three queries are being done because of the trigger:

1. INSERT INTO Cars_In (CarId, Amount) VALUES (1,2)
2. TRIGGER Cars_In_I
3. SELECT SCOPE_IDENTITY() AS ID

When SET NOCOUNT IS ON, the 2nd step does not return any result set (when you run it on SS Management Studio, it says Command(s) completed successfully.). So you only have two result sets from your query: steps 1 and 3. Thus you only need to do sqlsrv_next_result($stmt) once to skip the result set from step 1 to get the result set you want from SELECT SCOPE_IDENTITY() AS ID.

When SET NOCOUNT IS OFF, the 2nd step now returns a result set (when you run it on SS Management Studio, it says (1 row(s) affected)). Now you have three result sets: steps 1, 2, and 3. Thus you'll need to do sqlsrv_next_result($stmt) twice to skip result sets from steps 1 and 2 to get the result set you want from SELECT SCOPE_IDENTITY() AS ID.

In conclusion, you should add one more sqlsrv_next_result($stmt) to your PHP code to get the SCOPE_IDENTITY:

$query = 'INSERT INTO Cars_In (CarId, Amount) VALUES (1,2); SELECT SCOPE_IDENTITY() AS ID';
$stmt = sqlsrv_query($conn, $query);
sqlsrv_next_result($stmt);    // skips result set in 'INSERT INTO Cars_In (CarId, Amount) VALUES (1,2)'
sqlsrv_next_result($stmt);    // skips result set in TRIGGER Cars_In_I
sqlsrv_fetch($stmt);
$scope = sqlsrv_get_field($stmt, 0);
die($scope);

[genyslt]

Command(s) completed successfully <-- is this result? I don't see practical opportunities to use this as result.

How can i get number of results? How can i get last result?

To get scope i must write 4 lines of code:

sqlsrv_next_result($stmt);    // skips result set in 'INSERT INTO Cars_In (CarId, Amount) VALUES (1,2)'
sqlsrv_next_result($stmt);    // skips result set in TRIGGER Cars_In_I
sqlsrv_fetch($stmt);
$scope = sqlsrv_get_field($stmt, 0);

If my trigger have 10 calculations - how my code looks like? Is this normal way?

/* DON'T FORGET -> SCOPE IDENTITY IS LAST CONDITION ON QUERY */
function mssql_scope_identity($stmt){
    while (sqlsrv_next_result($stmt)){
        sqlsrv_fetch($stmt);
        $scope = sqlsrv_get_field($stmt, 0);
    }

    return $scope;
}

Anyway, scope_identity() selection is very often operation, i don't understand why this function is not native on the driver? (Ex: sqlsrv_scope_identity($stmt));

The good collection of functions on the driver solves many problems, saves developer's time, lines of code, the probability of making mistakes, highest performance and so on...

Save programmers time for using your driver.. Function's must allow flexibility, but here must be functions to get wanted results on easy way.

[yukiwongky]

@genyslt ,

"Command(s) completed successfully" is not a result. "(1 row(s) affected)" is. And yes, your while loop is the way to go, assuming the scope identity query is always the last query.

Thank you for your suggestion in creating a new function. We will take it in consideration.

[genyslt]

Any progress?

[yukiwongky]

Since scope_identity can be easily retrieved by sqlsrv_query($conn, "SELECT SCOPE_IDENTITY() AS ID"), we've decided we are not going to design another API for this.