mysql insert statement as a variable

Question

is it possible to set a variable to an insert statement in a stored procedure?

Something like:

set variable1 = insert into table(field1, field2, field3) values(val1, val2, variable2);

If so, how should it be written?

I keep throwing errors and documentation in the wild is inconclusive.

I was going for brevity but the entire procedure is thus:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_accession`(in barcode int, in accepted  varchar(11), in wt float(11,2), in wtunit varchar(3),in draw date, in chist varchar(256), in ghist varchar(256), in meds varchar(256), in diffdiag varchar(256),in diseasesus varchar(256),in volume int, in facility int, in patient int, in employ int, in compromised int, in receiving int, in test int)
BEGIN
declare accessionId int;
declare accessionTest int;
declare tkInsert varchar(256);
declare hptInsert varchar(256);
declare calInsert varchar(256);
declare pthInsert varchar(256);
declare vitdtkInsert varchar(256);
declare cnpInsert varchar(256);

if wtunit = 'lb' then set wt = convertLbKg(wt);
end if;

INSERT INTO accession(barcode_accession,accepted_accession,weight_accession,req_weight_units,draw_date_accession,cancer_history_accession,general_history_accession,medication_accession,differential_diagnosis_accession,disease_suspect_accession,volume_accession,facility_doctor_index_id_facility_doctor_index,patient_id_patient,employee_id_employee,accession_compromised_id_accession_compromised,receiving_id,accession_typeof_id_accession_typeof)
VALUES (barcode,accepted,wt,wtunit,draw,chist,ghist,meds,diffdiag,diseasesus,volume,facility,patient,employ,compromised,receiving,1);

set accessionId = last_insert_id();

set tkInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,1,'Pending');
set hptInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,2,'Pending');
set calInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,3,'Pending');
set pthInsert = insert into pending(accession_facility_index,reagent_type,`status`)values(accessionId,4,'Pending');

if test = 1 then tkInsert,calInsert;
elseif test =2 the hptInsert,pthInsert;
else pthInsert;
end if; 


END

What do you expect the variable to be set to? `INSERT` doesn't return a result set. — Barmar, Jan 09 '15 at 01:57
I updated my original question with the entire stored procedure. It's a little complicated but I think it gets the idea across. — kevincicero, Jan 09 '15 at 02:21
Do you want to store the query result, or define a procedure which execute the query? — didierc, Jan 09 '15 at 02:23
I wish to define a procedure to execute the insert query. However, there are about 8 different variations of the insert so just to keep DRY I thought an insert variable would be called for(if it exists) — kevincicero, Jan 09 '15 at 02:28
My guess is you want prepared statements: https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure — didierc, Jan 09 '15 at 02:28
you're right that is exactly what I need. New things to learn! — kevincicero, Jan 09 '15 at 02:36

score 2 · Answer 1 · edited May 26 '16 at 15:16

2

It is not very clear what you want but if your goal is to store insert's outcome (affected rows) you could use ROW_COUNT().

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT.
For other statements, the value may not be meaningful.

For example:

variable1 = (select ROW_COUNT());

edited May 26 '16 at 15:16

Fabian N.

3,807
2
23
46

answered Jan 09 '15 at 02:06

alexanderg

177
1
9

score 0 · Answer 2 · answered Jan 09 '15 at 02:27

0

I think you're looking for prepared statements.

PREPARE tkInsert FROM CONCAT("insert into pending(accession_facility_index,reagent_type,`status`)values(?, 1,'Pending')";

Then you execute it with:

EXECUTE tkInsert USING accessionId;

answered Jan 09 '15 at 02:27

Barmar

741,623
53
500
612

Is a prepared query a universal query like a procedure or function? – kevincicero Jan 09 '15 at 02:33

mysql insert statement as a variable

2 Answers2