2

I want to return the AutoID when inserting new row in the table using stored procedure. Below is the stored procedure, based on where clause i get rows and then insert same rows in the table with new ID; I need this new ID.

create or replace PROCEDURE duplicate_ticket
(
   i_RowID IN Varchar2
)AS

BEGIN

INSERT INTO main_table (
    programname,
    partnumber
) 
SELECT
    programname,
    partnumber
FROM
    main_table
WHERE id = i_RowID;

END duplicate_ticket;

I even tried the below reference but it did not work oracle procedure returns integer

Thanks

janaradanau
  • 153
  • 4
  • Possible duplicate of [Inserting into Oracle and retrieving the generated sequence ID](https://stackoverflow.com/questions/5558979/inserting-into-oracle-and-retrieving-the-generated-sequence-id) – mjwills Feb 21 '19 at 07:10
  • @mjwils - main_table is already created and it has 3 columns ID,programname and partnumber. – janaradanau Feb 21 '19 at 07:10
  • @mjwills - i tried even the link suggested by you.. it did not work – janaradanau Feb 21 '19 at 08:14
  • Please update your question to show what you tried based on my suggestion (leave your old code there, just edit to show the new code as well). Be sure to also include the C# you are using to call the stored proc. – mjwills Feb 21 '19 at 11:01

1 Answers1

0

you can try one of my simple example below: if anyone Need same thing in SQL 

In SQL : You can try 

CREATE PROCEDURE Add_Employee

@Emp_Name     CHAR(100),
@Emp_Dept     CHAR(50), 
@Emp_Address  CHAR(30),
@Emp_City     CHAR(25),
@Emp_Number   CHAR(10), 
@Emp_Salary   CHAR(30)
AS
BEGIN
declare @id int;
INSERT INTO Employee
(EmpName, EmpDept,Address,City,Phone,Salary)

VALUES( @Emp_Name ,@Emp_Dept , @Emp_Address ,@Emp_City , @Emp_Number, @Emp_Salary)
set @id=SCOPE_IDENTITY();
return @id
Anam Azam Khan
  • 47
  • 1
  • 1
  • 10