Get Id from last inserted record

[UncleCake]

Hi,

Is there a proper way to get the auto number Id of a recorded that was just inserted? The only way I know is to select the max. What is the preferred way to get it?

-Uncle Cake

 

[balves]

Use @@Identity immediately after the INSERT

From the BOL:

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

 

[UncleCake]

Do I use a RecordSet to get the IDENITY?

-Uncle Cake

 

[balves]

Where are you creating the record? VB, ASP or some other programming language? If so, then you cannot use @@Identity unless you're calling a stored proc.

If you're using a .AddNew function of a recordset in VB/ASP, then you're stuck with the doing a Max() after doing a .Update to the recordset.

If you can use a stored proc from VB/ASP to create the new record, then the proc can return the new Id value as described above as an output parameter to the proc.

 

[bperry]

Hi there,
Adding on to <balves> comments, it's usually considered good practice to be doing the Insert from inside a stored procedure. In that situation, the SP is not ordinarily returning a recordset to you (i.e. since you are Inserting, not Selecting), and so most times you would have the SP return the @@Identity value as an Output parameter instead of a recordset.

When returning just a single value like an Identity number, an output parameter will be more efficient (generate less network traffic) than a recordset.

brian perry

 

[UncleCake]

Thanks guys, I will try that. I don't know a lot about SP, but I will try it.

Thanks again,
-Uncle Cake

 

[UncleCake]

Hey balves,

Sorry I missed part of your question. I am working on a asp page that is accessing dlls, which have access to stored procedures.

Maybe you guys can help me out a bit more. I have created the SP that is below. Is it correct?

CREATE PROCEDURE hhb_InsertNewCompany

/* Variable Declaration */
@WebCompanyName nvarchar(255),
@WebCompanyStreet nvarchar(50),
@WebCompanyCity nvarchar(255),
@WebCompanyState nvarchar(50),
@WebCompanyZip nvarchar(255),
@Division nvarchar(255),
@ZipPlusFour nvarchar(50),
@Suspended int,
@Deleted int,
@PhoneNumber nvarchar(50),
@BarCustomerNumber nvarchar(50),
@WebCompanyCountry nvarchar(50),
@MailCode char(10)


AS

INSERT INTO tblWebCompanies
(WebCompanyName, WebCompanyStreet, WebCompanyCity, WebCompanyState, WebCompanyZip,
Division, ZipPlusFour, Suspended, Deleted, PhoneNumber, BarcustomerNumber, WebCompanyCountry, MailCode)
VALUES
(@WebCompanyName, @WebCompanyStreet, @WebCompanyCity, @WebCompanyState, @WebCompanyZip,
@Division, @ZipPlusFour, @Suspended, @Deleted, @PhoneNumber, @BarcustomerNumber, @WebCompanyCountry, @MailCode)
SELECT @@IDENTITY AS 'Identity'
GO

 

[bperry]

I think this is an example of returning the identity as a recordset. i.e. a recordset with one row and one column. As long as the calling client expects to see a recordset back, i imagine this would work.
------------------------------------
However, I think returning an identity number is more commonly done with an output parameter in the SP. If you wanted to set this up, your client has to get set to look for and see the output parameter when it is passed back from the SP. And within the SP, you would make these changes:

(1) Add something like this to your 'Variables Declaration' section:
@parmRowcount int = 0 output

(the output keyword alerts the SP that this parameter must be passed back)

(2) Change SELECT @@IDENTITY AS 'Identity' to
select @parmIdentity = Scope_Identity()

Hopefully, that should be it, if I haven't screwed it up. This is all only if you wanted to use the output parameter approach.
-----------------------------------------

 

[bperry]

I think this is an example of returning the identity as a recordset. i.e. a recordset with one row and one column. As long as the calling client expects to see a recordset back, i imagine this would work.
------------------------------------
However, I think returning an identity number is more commonly done with an output parameter in the SP. If you wanted to set this up, your client has to get set to look for and see the output parameter when it is passed back from the SP. And within the SP, you would make these changes:

(1) Add something like this to your 'Variables Declaration' section:
@parmIdentity int = 0 output

(the output keyword alerts the SP that this parameter must be passed back)

(2) Change SELECT @@IDENTITY AS 'Identity' to
select @parmIdentity = Scope_Identity()

Hopefully, that should be it, if I haven't screwed it up. This is all only if you wanted to use the output parameter approach.
-----------------------------------------

 

[bperry]

Sorry about that.
I apologize for those last two double posts.
Please ignore the first one, which contains a small error, and read the second one (if you wish).
bp

 

[UncleCake]

Thanks Berry, I am very new to this so I will take all of the help I can. I changed it to:

CREATE PROCEDURE hhb_InsertNewCompany


/* Variable Declaration */
@WebCompanyName nvarchar(255),
@WebCompanyStreet nvarchar(50),
@WebCompanyCity nvarchar(255),
@WebCompanyState nvarchar(50),
@WebCompanyZip nvarchar(255),
@Division nvarchar(255),
@ZipPlusFour nvarchar(50),
@Suspended int,
@Deleted int,
@PhoneNumber nvarchar(50),
@BarCustomerNumber nvarchar(50),
@WebCompanyCountry nvarchar(50),
@MailCode char(10),
@parmIdentity int = 0 output


AS

INSERT INTO tblWebCompanies
(WebCompanyName, WebCompanyStreet, WebCompanyCity, WebCompanyState, WebCompanyZip, Division, ZipPlusFour, Suspended, Deleted, PhoneNumber, BarcustomerNumber, WebCompanyCountry, MailCode)
VALUES
(@WebCompanyName, @WebCompanyStreet, @WebCompanyCity, @WebCompanyState, @WebCompanyZip, @Division, @ZipPlusFour, @Suspended, @Deleted, @PhoneNumber, @BarcustomerNumber, @WebCompanyCountry, @MailCode)
SELECT @parmIdentity = Scope_Identity()
GO


I guess if that is correct, I will start to work on calling it from VB. This may take even longer for me. By the way, thanks for all of the help that you have given me.

-Uncle Cake

 

[balves]

I believe that the Scope_Identity() function is new to SQL Server 2000. Earlier versions, use @@Identity.

Just from an VB/ASP efficiency aspect, if you're calling an stored proc to do an INSERT, UPDATE or DELETE, using an ADO Command object is more efficient than using an ADO recordset.

 

[UncleCake]

Is there an example somewhere that I can look at to call this SP and get the ID back? I am using VB.

-Uncle Cake