Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Get Id from last inserted record
- Thread starter UncleCake
- Start date
- Thread starter
- #3
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.
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.
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
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
- Thread starter
- #6
- Thread starter
- #7
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
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
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.
-----------------------------------------
------------------------------------
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.
-----------------------------------------
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.
-----------------------------------------
------------------------------------
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.
-----------------------------------------
- Thread starter
- #11
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
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
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.
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.
- Thread starter
- #13
- Status
Similar threads
- Locked
- Question