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

Question

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.

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.

score 1 · Accepted Answer · answered Nov 24 '21 at 18:41

1

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;

answered Nov 24 '21 at 18:41

RobertT

136
4

In th eend i used a Combination of the Answers from Charlieface and RoberT. This one is works out of the Box. But wihout the Declaration and Selection Part from this Answer i would not have found my Solution... Thanks you Both – Quelltextknecht Nov 27 '21 at 11:46

Charlieface · Answer 2 · 2021-11-27T18:56:04.300

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

Declare @IndexField Bigint;
UPDATE Artikel 
SET [SuchBeg] = 'TEST', 
    @IndexField = [IndexField] 
WHERE [ArtNr] = '19904.5'

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

    SET @IndexField = SCOPE_IDENTITY();
END;

SELECT @IndexField;

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;

SELECT @IndexField;

Thanks I Used a combination Of the Answers from Charlieface and RoberT. I Edited the First SQL to Work under ADO. It Needed the Declarition and Selection of @IndexField. Thanks you Both — Quelltextknecht, Nov 27 '21 at 11:43

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

2 Answers2