Return identity inserted

Question

Return identity inserted

Paul Cresham

Hall of Fame

Points: 3623
More actions
April 19, 2005 at 10:16 am

#68972

Guys, my brain's fried. Been staring at code all day!
I'm writing a small front-end to a job control system that creates job records. Part of this involves an INSERT to a master jobs table with the Job Number as an IDENTITY field. I want to return the Job Number to the front end app to present to the user.
It is rare that two people will use the application at the same time, so "SELECT MAX(JobNumber)" is a possible option, but one I obviously want to avoid. Another option is to have the app itself generate the Job Number but this presents similar problems.
Any ideas how I can do this whilst retaining the field as an IDENTITY?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

You can use a autogenerated identity column and use SCOPE_IDENTITY() to get the identity value from the last insert within the current scope. Check Booksonline for more info.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 2

Thanks--I knew there was something. I'll embed the INSERT in a stored procedure with a return value of SCOPE_IDENTITY() and would hope that does the trick.

Thanks again!

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

Yes. You can also use @@IDENTITY but that will not always guarantee you about the integrity of the IDentity value where as SCOPE_IDENTITY() is more reliable.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Interestingly, you need to look at IDENT_CURRENT in BOL to see why SCOPE_IDENTITY() is preferable to @@IDENTITY

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 5

Even SCOPE_IDENTITY when looked up in BOL has enough information about @@IDENTITY, IDENT_INDENT and SCOPE_IDENTITY() and which one of them is better.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Aargh, okay, I was a little bit imprecise. I had the example in mind that you can find under IDENT_CURRENT.

Btw, what is IDENT_INDENT?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 7

Sorry I meant IDENT_CURRENT

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

DanaH1976 Ten Centuries Points: 1046 More actions · Answer 8

Set an output parameter and pass the identity value to it, as follows:

CREATE PROCEDURE upd_DLCourseOneStudent

@SocialSecurityNumber char(9),

@CourseNumber varchar(50),

@Semester varchar(50),

@NoOfCredit tinyint,

@DLCourseID int OUTPUT

INSERT INTO tblDLCourses

(SocialSecurityNumber,

CourseNumber,

Semester,

NoOfCredit)

VALUES

(@SocialSecurityNumber,

@CourseNumber,

@Semester,

@NoOfCredit)

SET @DLCourseID = @@IDENTITY

Dana
Connecticut, USA
Dana

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

You do have read the drawbacks of using @@IDENTITY, haven't you?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]