ADO Update Or Insert is missing Output Inserted Value for second Execute Branch-CodePudding

I am using a TADOQuery with SQL Server 2019 and Delphi 10.4 Update 2. I am trying to solve a update or insert operation in one SQL statement.

I have defined the column IndexField as an auto-incrementing column.

This is my (simplified) SQL Server table:

CREATE TABLE [dbo].[Artikel]
(
    [SuchBeg] [varchar](25) NULL,
    [ArtNr] [varchar](25) NULL,
    [IndexField] [bigint] IDENTITY(1,1) NOT NULL,
    PRIMARY KEY CLUSTERED ([IndexField] ASC) 
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Simplified SQL statement:

UPDATE Artikel 
SET [SuchBeg] = 'TEST', 
    [ArtNr] = '19904.S' 
OUTPUT INSERTED.[IndexField] 
WHERE [ArtNr] = '19904.S'

IF @@ROWCOUNT = 0
    INSERT INTO Artikel ([SuchBeg], [ArtNr]) 
    OUTPUT Inserted.[IndexField] 
    VALUES ('TEST', '19904.S');

Delphi source:

Query := TADOQuery.Create(nil);
Query.Connection := ADOConnection;
try
  Query.SQL.Text := sSqlText;
  Query.Open;
  Memo1.Lines.Add('Ado Result: '  Query.RecordCount.ToString   ' ~ '    Query.Fields[0].AsString);
finally
  Query.Free;
end;

if i hit the "Update Part" in my SQL i can read a 1 Row 1 Field Recordset from the Query. BUT if the Second "Insert Part" is executetd i dont get a Result (0 Row, 1 Field (Bigint) with 0).

If i execute either "Update" or "Insert" without the "if Rowcount" it works.

if the Statement is Executet in SQL Server Management Studio it Works, and shows 2 Result Windows. First 0 Rows Affected and Second with the desired Value and 1 Row affected.

Is it possible to do this in one go?

Alternative Upsert Ways are here ... but i did not want to be a SQL Server superhero.

CodePudding user response：

Try storing the output to a table variable and then return the variable.

DECLARE @Output TABLE (IndexField BIGINT);

UPDATE Artikel SET [SuchBeg] = 'TEST', [ArtNr] = '19904.S' OUTPUT INSERTED.[IndexField] INTO @Output WHERE [ArtNr] = '19904.S'
if @@ROWCOUNT = 0
INSERT INTO Artikel ([SuchBeg], [ArtNr]) Output Inserted.[IndexField] INTO @Output VALUES ('TEST', '19904.S');

SELECT * FROM @Output;

CodePudding user response：

If you are only updating one row you can use an OUTPUT parameter. I'm not sure the Delphi side, but the SQL would look like this

UPDATE Artikel 
SET [SuchBeg] = 'TEST', 
    @IndexField = [IndexField] 
WHERE [ArtNr] = '19904.S'

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Artikel ([SuchBeg], [ArtNr]) 
    VALUES ('TEST', '19904.S');

    SET @IndexField = SCOPE_IDENTITY();
END;

I must say, I didn't see the point in updating the ArtNr column to the same value

Note that if you want to make this ACID-transaction compliant, you would use the following hints

SET XACT_ABORT ON;  -- ensures rollback

BEGIN TRAN;

UPDATE Artikel WITH (UPDLOCK, HOLDLOCK)
SET [SuchBeg] = 'TEST', 
    @IndexField = [IndexField] 
WHERE [ArtNr] = '19904.S'

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Artikel ([SuchBeg], [ArtNr]) 
    VALUES ('TEST', '19904.S');

    SET @IndexField = SCOPE_IDENTITY();
END;

COMMIT;