How to obtain upsert results ?

[MathieuNls]

Hello,

Thanks for your driver !

I followed code examples from https://sqlperformance.com/2020/09/locking/upsert-anti-pattern and particularly this one:

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END
 
COMMIT TRANSACTION;

To get the id being upserted I used:

Declare @ID TABLE(InnerID INT null);
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val 
OUTPUT inserted.id INTO @ID # I also tried without the INTO
 WHERE [key] = @key;
 
IF @@ROWCOUNT != 0
  SELECT InnerID from @ID;
ELSE
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
  select SCOPE_IDENTITY() as InnerID;
END

I tried to put this query with and without a prepared statement and through Exec, Query, and QueryRow and with and without sql.Named("ID", sql.Out{Dest: &reportID}), but I was never able to get back the ID.

In SQL Server Management Studio the above query returns one line with one col called InnerID for update and insert.

I am no expert in T-SQL and am new to this driver. There's something I likely don't understand / doing wrong.

Thanks for your help.

[MathieuNls]

Replacing Select InnerID from @ID by Select * from @ID is the solution to have Query correctly get the row.