devxlogo

How to capture return value of insert statement

How to capture return value of insert statement

Question:
I have a table, called bugs. I have an insert trigger on the bugs table that generates a unique id, called bug_id. When I perform an insert into bugs, it returns the bug_id it just assigned to that record.

I am writing a stored procedure called doit that will perform an insert into bugs. I want to know what the syntax is to allow me to capture the bug_id and use it for other actions.

Here is the code I have, which obviously won’t compile:

==========================create proc doit as   declare @bug_id int  select @bug_id = EXEC "insert into bugs (bug_id) values (0)"==========================

What is the correct syntax to allow me to do this?

Answer:
I’m not sure I understand how or why you used a trigger to return the value of the identity field that was inserted.

The global variable @@identity contains the value of the last identity field that was inserted. Therefore, after an insert you should have a statement such as select @my_variable = @@identity.

See also  Is Machine Learning Automating Creativity in Graphic Design?
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist