Return Primary Key that has just been created

I have the following Stored Procedure that works fine,

ALTER PROCEDURE "DBA"."New-Project" @creator varchar(3), @project varchar(40), @gutt integer, @cdate date
AS
INSERT INTO dba.lead (leadno, contno, leadsrccd, leaddate, createdby, leadsttscd, cg_code, leadsizecd, guttometer, estclose, lead_desc)
VALUES (getNextPrimary('lead'), 2036, 'DASHB', today(), @creator, 'QUOTE', 777, 'RF', @gutt, @cdate, @project)

What I would like to do is get the new 'leadno' that is created once the stored procedure has run. I have seen several references to @@identity, but I am not sure if this is what I should be using and if so how to use it? As currently I just get 0.

asa-9

asked 13 Mar '17, 07:57

Jongee
217●17●17●22
accept rate: 0%

@@identity returns the last generated system generated primary key. You appear to be using a function getNextPrimary().

Perhaps you could store the result of getNextPrimary and convert the New-Project to a function so that it returns that value.

(13 Mar '17, 08:17) Chris Keating

@Someone put a downvote on that question. It is helpful to tell why you did so.

(14 Mar '17, 12:07) Volker Barth

flat view

One Answer:

active answers oldest answers newest answers popular answers

In order to use the @@identity global variable, you need to insert a new record without supplying a value for the PK column declared with DEFAULT (GLOBAL) AUTOINCREMENT - to cite the docs:

The @@identity variable holds the most recent value inserted by the current connection into an IDENTITY column, a DEFAULT AUTOINCREMENT column, or a DEFAULT GLOBAL AUTOINCREMENT column, or zero if the most recent insert was into a table that had no such column.

In your case, you do supply a value via the function "getNextPrimary('lead')", so @@identity will return 0.

When the "leadno" PK column is declared with DEFAULT AUTOINCREMENT, you can simply omit the column in the INSERT column (or supply the special DEFAULT value for it), and then @@identity will return the automatically generated value, such as:

BEGIN
   INSERT INTO dba.lead (contno, leadsrccd, leaddate, createdby, leadsttscd, cg_code, leadsizecd, guttometer, estclose, lead_desc)
   VALUES (2036, 'DASHB', today(), @creator, 'QUOTE', 777, 'RF', @gutt, @cdate, @project);
   SELECT @@identity;
END;

Note: You can also use the builtin get_identity() function to let the server create the next value beforehand, i.e. you probably could replace your

getNextPrimary('lead')

call with

get_identity('lead')

in order to use a builtin function.

permanent link

answered 13 Mar '17, 08:23

Volker Barth
40.2k●361●549●822
accept rate: 34%

Amazingly enough, you CAN specify get_identity() in the VALUES clause and it will fill @@IDENTITY...

CREATE TABLE t (
   pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t ( data ) VALUES ( 111 );
COMMIT;
SELECT '@@IDENTITY # 1', @@IDENTITY;

'@@IDENTITY # 1',@@IDENTITY
'@@IDENTITY # 1',1

INSERT t ( pkey, data ) VALUES ( GET_IDENTITY ( 't' ), 111 );
COMMIT;
SELECT '@@IDENTITY # 2', @@IDENTITY;

'@@IDENTITY # 2',@@IDENTITY
'@@IDENTITY # 2',2

(13 Mar '17, 14:00) Breck Carter

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

asa-9 ×108

question asked: 13 Mar '17, 07:57

question was seen: 1,476 times

last updated: 14 Mar '17, 12:07

SAP SQL Anywhere

Return Primary Key that has just been created

Follow this question

Related questions