0

I have a code that insert a row and retrieve generated key from it. However, it doesn't work with SQL Server. I researched and seems like the column needs IDENTITY property.

However, I am already using Sequence in sql server to set property for that column. It starts at 1, increment by 1 and maximum value is certain value. My query uses the sequence to insert the row like this:

INSERT INTO event_master (event_id, user_id, type, detail, status, update_timestamp)
VALUES (NEXT VALUE FOR event_master_id_seq, ?,?,?,?,?)

Is there any way to use this format to retrieve generated value (in my case, event_id)? or do I have to have IDENTITY in order for it to work?

RIRAY
  • 62
  • 7
Jonathan Hagen
  • 580
  • 1
  • 6
  • 29
  • 2
    You would need to use an [`OUTPUT`](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15) clause. – Thom A Sep 24 '20 at 15:20
  • I think related answer , refer [https://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar#:~:text=When%20you%20insert%20a%20record,a%20scalar%20SELECT%20SCOPE_IDENTITY()](https://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar#:~:text=When%20you%20insert%20a%20record,a%20scalar%20SELECT%20SCOPE_IDENTITY()) – RIRAY Sep 24 '20 at 15:30

1 Answers1

1

I wanted to close this as a duplicate of this one, but that question doesn't address or ask about using sequences instead of identity columns. Also I wanted to make it clear that this works with multiple rows (in case you planned on using a scalar variable).

DECLARE @event_ids TABLE(event_id int);

INSERT INTO dbo.event_master (event_id, ... other cols)
OUTPUT inserted.event_id INTO @event_ids
VALUES (NEXT VALUE FOR event_master_id_seq, ... other vals);

SELECT event_id FROM @event_ids;

As an aside, I really hope you are not using ? placeholders from some application, you should really use explicit and strongly-typed parameters. I know this is harder in Java than more modern languages but see this answer, for example.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490