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
asked 13 Mar, 07:57
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:
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
in order to use a builtin function.
answered 13 Mar, 08:23