How to get autonumber assigned to the insert ?

How to get autonumber assigned to the insert ?

Post by Jean » Fri, 13 Sep 2002 02:27:33



Hi,

I have a user table that contain a autonumber ID as primary key. I would to
write a stored procedure to insert record into this table and also returns
the autonumber ID as the return value of the stored procedure.  But, how do
I capture the autonumber generated by each insert transaction  since Insert
don't return any value.

Thanks

Jean

 
 
 

How to get autonumber assigned to the insert ?

Post by C.Sivakuma » Fri, 13 Sep 2002 03:18:53


Hi,
-- First create a SP like this:-
-- ***********
CREATE PROCEDURE [dbo].[SP_TestSiva]
AS

begin

        insert into tstSiva(fld1) values ('row1test')


end
GO
-- ***********
-- then run it like
exec sp_testSiva
-- you will see something like this:-
FD2A5788-D7D6-45CC-9BC1-26E49FC8C420

select * from tstSiva
FD2A5788-D7D6-45CC-9BC1-26E49FC8C420    row1test  

hope that helps!!
Also do you know
Is there any date or AddMonth functions in SQL Server 2000
at all?
Basically I am trying to add a month to a given date
inside a Stored Procedure
For example like :

-- trying to add one month from today's date

-- trying to add two months from todays date

thanks a lot
siva

Quote:>-----Original Message-----
>Hi,

>I have a user table that contain a autonumber ID as

primary key. I would to
Quote:>write a stored procedure to insert record into this table
and also returns
>the autonumber ID as the return value of the stored

procedure.  But, how do
Quote:>I capture the autonumber generated by each insert

transaction  since Insert
Quote:>don't return any value.

>Thanks

>Jean

>.


 
 
 

How to get autonumber assigned to the insert ?

Post by Dan Guzma » Fri, 13 Sep 2002 06:18:58


I assume you mean IDENTITY column?  In this case, you can obtain the
last identity value inserted on the current connection with

value as an output parameter, return value or recordset.  The example
below illustrates all three techniques.

CREATE TABLE MyTable
    (
        ID int NOT NULL IDENTITY(1,1)
    )
GO

CREATE PROC usp_MyProc

AS
SET NOCOUNT ON
INSERT INTO MyTable DEFAULT VALUES



GO






GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> Hi,

> I have a user table that contain a autonumber ID as primary key. I
would to
> write a stored procedure to insert record into this table and also
returns
> the autonumber ID as the return value of the stored procedure.  But,
how do
> I capture the autonumber generated by each insert transaction  since
Insert
> don't return any value.

> Thanks

> Jean

 
 
 

How to get autonumber assigned to the insert ?

Post by Wayne Snyde » Fri, 13 Sep 2002 13:01:57


Use Scope_Identity() as Dan has suggested..  It is the best method.

--
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com

I support the Professional Association for SQL Server (PASS) and its user
community of SQL Server Professionals.
www.sqlpass.org

 
 
 

How to get autonumber assigned to the insert ?

Post by Scott Ive » Fri, 13 Sep 2002 16:59:17



value.  If your insert fires a trigger with another insert where there is an
IDENTITY field, then the LAST INSERTED IDENTITY will be returned, not the
IDENTITY from the table you are looking for.  If this is the case, I'd do
the insert with a table lock (INSERT tablename WITH(TABLOCKX)...) and then
do a SELECT MAX(idfieldname).

Scott


Quote:> Use Scope_Identity() as Dan has suggested..  It is the best method.

> --
> Wayne Snyder, SQL Server MVP
> Computer Education Services Corporation (CESC), C*te, NC
> www.computeredservices.com

> I support the Professional Association for SQL Server (PASS) and its user
> community of SQL Server Professionals.
> www.sqlpass.org

 
 
 

How to get autonumber assigned to the insert ?

Post by shaun2761 » Thu, 30 Jan 2003 23:27:00


I have had errors running the above methods when there are large numbers
of users Inserting at the same time. The method that worked here is
creating a Trigger that returns that ID FROM INSERTED. Just be sure that
your application is ready to handle a return value on the insert.

--
Posted via http://dbforums.com