Search code examples
sql-serverdelphiado

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

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; // not ExecSQL; if Results are expected: Open does the Trick.
  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.

View of SQL Management Studio

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.

Solution

  • 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;