QSqlQuery fails to get SQL Server stored procedure output value-CodePudding

I am converting web server code written in VB.NET to Qt 5.15.12. The server accesses a Microsoft SQL Server 2012 database. I have several stored procedures that take an output parameter. The output parameter works as expected with VB.net. One stored procedure is giving me issues in Qt. The stored procedure checks if a record exists in a table. If not, it adds it. If so, it updates it. The stored procedure initially returned @@ROWCOUNT but I could not figure out how to get Qt to give me the stored procedure return value so I added an output parameter. The stored procedure essentially looks like this:

CREATE PROCEDURE AddUpdateRecord
    @Param1 NChar(12), 
    @Param2 NVarChar(80),
    @RetVal Int Output
AS
BEGIN
    SET NOCOUNT ON
    SET @RetVal = 0
    SELECT Column1 FROM MyTable WHERE Column1=@Param1

    IF @@ROWCOUNT > 0
    BEGIN
        UPDATE MyTable SET Column2=@Param2 WHERE Column1=@Param1
        SET @RetVal = @@ROWCOUNT
    END
    ELSE
    BEGIN
        INSERT INTO MyTable (Column1, Column2) VALUES (@Param1, @Param2)
        SET @RetVal = @@ROWCOUNT
    END
END

The Qt code that calls the stored procedure looks like this:

    QSqlQuery   qry(dbObject);
    qry.prepare("execute AddUpdateRecord ?, ?, ?);
    qry.bindValue(0, "012345678912");
    qry.bindValue(1, "ABCDEFGHIJKLMNOP");
    qry.bindValue(2, QVariant(int(-1)), QSql::Out);
    if (  qry.exec() && (qry.boundValue(2).toInt() > 0) )
    {
        return(true);
    }
    return(false);

If I call the stored procedure with a key that does NOT exist, I successfully get the output value. If I call it with a key that DOES exist, the bound value does not change from -1. Am I doing something wrong that is preventing Qt from getting the output value or is this a bug in Qt?

CodePudding user response：

A bare SELECT in the body of the stored procedure sends a resultset to the client.

SELECT Column1 FROM MyTable WHERE Column1=@Param1

The output parameter is sent after the resultset in the TDS response, and in many client libraries you must consume the resultset before checking the output parameters. You can avoid this and improve this procedure by removing a race condition like this:

CREATE PROCEDURE AddUpdateRecord
    @Param1 NChar(12), 
    @Param2 NVarChar(80),
    @RetVal Int Output
AS
BEGIN
    SET NOCOUNT ON
    SET @RetVal = 0
    BEGIN TRANSACTION

    IF EXISTS (SELECT * FROM MyTable  with (updlock,holdlock) WHERE Column1=@Param1) 
    BEGIN
        UPDATE MyTable SET Column2=@Param2 WHERE Column1=@Param1
        SET @RetVal = @@ROWCOUNT
    END
    ELSE
    BEGIN
        INSERT INTO MyTable (Column1, Column2) VALUES (@Param1, @Param2)
        SET @RetVal = @@ROWCOUNT
    END
    COMMIT TRANSACTION
END