how to output a parameter using a stored procedure in mysql?

Question

I have two tables say

table1:

create table Person(
  Id int primary key auto_increment,
  name varchar(20),
  Gender char(1)
);

table2:

create table Details(
  Age int,
  Phone int(10) primary key,
  Address varchar(100)
  Id foreign key(Id) references Person(Id),
  Name foreign key(Name) references Person(Name)
);

i will have to create a stored procedure to insert data into table:'Details'

create procedure usp_insert(
  IN Name varchar(100),
  IN Age int,
  IN Phone int(10),
  IN Address varchar(100),
  OUT Id int
)
begin

//want to output the Id as QW001 on inserting the data into the Details table.

insert into Details(Name,Age,Phone,Address) values(name,age,phone,address)
end

How can i achieve the Id in the following format 'QW001' as output parameter. Can someone help me out in correcting the above stored procedure,since i'm new to this. Any help is appreciated. TYIA!

MySQL auto-increment columns are integers only. See https://stackoverflow.com/questions/23331194/concatenating-a-string-and-primary-key-id-while-inserting/23331246#23331246 — Bill Karwin, Sep 18 '17 at 15:40

colapiombo · Answer 1 · 2017-09-18T16:02:56.640

0

you need to use LAST_INSERT_ID() like this :

create procedure usp_insert(
  IN Name varchar(100),
  IN Age int,
  IN Phone int(10),
  IN Address varchar(100),
  OUT Id int
)
begin

insert into Details(Name,Age,Phone,Address) values(name,age,phone,address);
set Id =CONCAT("QW00", LAST_INSERT_ID()) AS ConcatenatedString;
end

edited Sep 18 '17 at 16:02

answered Sep 18 '17 at 15:33

colapiombo

179
1
3
13

But I wanted the Id to be in the following format 'QW001' – Kenz Sep 18 '17 at 15:37
Is there any other way I can concat or something? – Kenz Sep 18 '17 at 15:43
you can use CONCAT() function of mysql – colapiombo Sep 18 '17 at 15:55
Will this id get inserted whenever a new row is added to the table? – Kenz Sep 18 '17 at 16:05
the id you put out from the procedure is a concat from the primary_key and the text . if you want to save in another table is a different request.please explain better what you want to do – colapiombo Sep 18 '17 at 16:18
I want it as a new column – Kenz Sep 19 '17 at 01:59
if this new column is varchar you can concat() this value and then use SET function for save the value in a variable and then do an insert. – colapiombo Sep 19 '17 at 08:23

how to output a parameter using a stored procedure in mysql?

1 Answers1