Very Computer

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

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

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

Quote:>I capture the autonumber generated by each insert

Quote:>don't return any value.

>Thanks

>Jean

>.

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

--
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

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

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