Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How do I get autonumber field value in Stored Procedure.

5 posts in General Discussion Last posting was on 2003-09-10 13:23:50.0Z
Chandresh Patel Posted on 2003-09-09 20:58:43.0Z
Sender: ab4.3f5e3e17.1804289383@sybase.com
From: Chandresh Patel
Newsgroups: ianywhere.public.general
Subject: How do I get autonumber field value in Stored Procedure.
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f5e3f03.ac9.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Sep 2003 13:58:43 -0700
X-Trace: forums-1-dub 1063141123 10.22.241.41 (9 Sep 2003 13:58:43 -0700)
X-Original-Trace: 9 Sep 2003 13:58:43 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1729
Article PK: 3950

I create one store procedure that add customer and order
record.

Customer table has one autonumber field and i use that field
as a FK in Order table so after inserting record in to
customer how can i get added autonumber value.

I know some other trick (Find Maximum) but i don't want to
do that.

Is there any way to get value.

Thanks for reading
Chandresh Patel


Paul Horan[TeamSybase] Posted on 2003-09-09 22:35:19.0Z
From: "Paul Horan[TeamSybase]" <paulhATvcisolutionsDOTcom>
Newsgroups: ianywhere.public.general
References: <3f5e3f03.ac9.846930886@sybase.com>
Subject: Re: How do I get autonumber field value in Stored Procedure.
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: ny-chicagost2d-63.buf.adelphia.net
Message-ID: <3f5e560a$1@forums-2-dub>
X-Original-Trace: 9 Sep 2003 15:36:58 -0700, ny-chicagost2d-63.buf.adelphia.net
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 9 Sep 2003 15:32:34 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 9 Sep 2003 15:35:19 -0700
X-Trace: forums-1-dub 1063146919 10.22.108.75 (9 Sep 2003 15:35:19 -0700)
X-Original-Trace: 9 Sep 2003 15:35:19 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1732
Article PK: 3953

The global variable @@identity will always contain the last autoincrement value generated by that connection.

--
Paul Horan[TeamSybase]

Get the new PB9 books!
http://www.pb9books.com?source=newsgroups

Code samples on Sybase CodeXchange:
http://codexchange.sybase.com

ISUG Enhancement Requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

<Chandresh Patel> wrote in message news:3f5e3f03.ac9.846930886@sybase.com...
> I create one store procedure that add customer and order
> record.
>
> Customer table has one autonumber field and i use that field
> as a FK in Order table so after inserting record in to
> customer how can i get added autonumber value.
>
> I know some other trick (Find Maximum) but i don't want to
> do that.
>
> Is there any way to get value.
>
> Thanks for reading
> Chandresh Patel


Chandresh Patel Posted on 2003-09-09 23:59:41.0Z
Sender: bda.3f5e68a9.1804289383@sybase.com
From: Chandresh Patel
Newsgroups: ianywhere.public.general
Subject: Re: How do I get autonumber field value in Stored Procedure.
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f5e696d.bdf.846930886@sybase.com>
References: <3f5e3f03.ac9.846930886@sybase.com><3f5e560a$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Sep 2003 16:59:41 -0700
X-Trace: forums-1-dub 1063151981 10.22.241.41 (9 Sep 2003 16:59:41 -0700)
X-Original-Trace: 9 Sep 2003 16:59:41 -0700, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1733
Article PK: 3954

Thank you very much. But could you give me some sample code
of it.

> The global variable @@identity will always contain the
> last autoincrement value generated by that connection.
>
> --
> Paul Horan[TeamSybase]
>
> Get the new PB9 books!
> http://www.pb9books.com?source=newsgroups
>
> Code samples on Sybase CodeXchange:
> http://codexchange.sybase.com
>
> ISUG Enhancement Requests:
> http://www.isug.com/cgi-bin/ISUG2/submit_enhancement
>
> <Chandresh Patel> wrote in message
> > news:3f5e3f03.ac9.846930886@sybase.com... I create one
> > store procedure that add customer and order record.
> >
> > Customer table has one autonumber field and i use that
> > field as a FK in Order table so after inserting record
> > in to customer how can i get added autonumber value.
> >
> > I know some other trick (Find Maximum) but i don't want
> > to do that.
> >
> > Is there any way to get value.
> >
> > Thanks for reading
> > Chandresh Patel
>
>


Greg Fenton Posted on 2003-09-10 02:01:49.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4; MultiZilla v1.4.0.4A) Gecko/20030624
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: How do I get autonumber field value in Stored Procedure.
References: <3f5e3f03.ac9.846930886@sybase.com><3f5e560a$1@forums-2-dub> <3f5e696d.bdf.846930886@sybase.com>
In-Reply-To: <3f5e696d.bdf.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: vpn-concord-032.sybase.com
Message-ID: <3f5e866f$1@forums-2-dub>
X-Original-Trace: 9 Sep 2003 19:03:27 -0700, vpn-concord-032.sybase.com
Lines: 55
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 9 Sep 2003 18:59:02 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 9 Sep 2003 19:01:49 -0700
X-Trace: forums-1-dub 1063159309 10.22.108.75 (9 Sep 2003 19:01:49 -0700)
X-Original-Trace: 9 Sep 2003 19:01:49 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1734
Article PK: 3955


Chandresh wrote:
> Thank you very much. But could you give me some sample code
> of it.
>

Please, always post the version and build number of ASA that you are
using (e.g. use the command "dbeng8 -v")

Here is some sample code:

/* ------------------------------------------ */
CREATE PROCEDURE my_test
BEGIN
DECLARE @id INT;

INSERT INTO table1 (col2, col3, ...)
VALUES (...);

SET @id = @@identity;

INSERT INTO table2 (fk_col, ...)
VALUES (@id, ... );
END;
/* ------------------------------------------ */


A couple of points about above code:

- if you only need to insert the autoincrement value once, then
you could use "@@identity" instead of "@id" in the second insert
statement and avoid declaring and setting the @id variable.

- you could use SELECT instead of SET:
SELECT @@identity INTO @id;

I suggest that you read about the @@IDENTITY global variable in the
online docs. In the ASA 8.0.x docs, see:

Adaptive Server Anywhere SQL Reference Manual
1. SQL Language Elements
Variables
Global variables
- @@identity global variable


Hope thia helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Chandresh Patel Posted on 2003-09-10 13:23:50.0Z
Sender: 6037.3f5f2593.1804289383@sybase.com
From: Chandresh Patel
Newsgroups: ianywhere.public.general
Subject: Re: How do I get autonumber field value in Stored Procedure.
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f5f264b.6043.846930886@sybase.com>
References: <3f5e3f03.ac9.846930886@sybase.com><3f5e560a$1@forums-2-dub> <3f5e696d.bdf.846930886@sybase.com><3f5e866f$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 10 Sep 2003 06:25:31 -0700, 10.22.241.42
Lines: 64
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Sep 2003 06:21:01 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Sep 2003 06:23:50 -0700
X-Trace: forums-1-dub 1063200230 10.22.108.75 (10 Sep 2003 06:23:50 -0700)
X-Original-Trace: 10 Sep 2003 06:23:50 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1735
Article PK: 3957

Thank you veru much. I am 100% satisfied with your answer
and produ to get fast answer from you.

Chandresh

> Chandresh wrote:
> > Thank you very much. But could you give me some sample
> > code of it.
> >
>
> Please, always post the version and build number of ASA
> that you are using (e.g. use the command "dbeng8 -v")
>
> Here is some sample code:
>
> /* ------------------------------------------ */
> CREATE PROCEDURE my_test
> BEGIN
> DECLARE @id INT;
>
> INSERT INTO table1 (col2, col3, ...)
> VALUES (...);
>
> SET @id = @@identity;
>
> INSERT INTO table2 (fk_col, ...)
> VALUES (@id, ... );
> END;
> /* ------------------------------------------ */
>
>
> A couple of points about above code:
>
> - if you only need to insert the autoincrement value once,
> then
> you could use "@@identity" instead of "@id" in the
> second insert
> statement and avoid declaring and setting the @id
> variable.
>
> - you could use SELECT instead of SET:
> SELECT @@identity INTO @id;
>
> I suggest that you read about the @@IDENTITY global
> variable in the online docs. In the ASA 8.0.x docs, see:
>
> Adaptive Server Anywhere SQL Reference Manual
> 1. SQL Language Elements
> Variables
> Global variables
> - @@identity global variable
>
>
> Hope thia helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
>