CODEWITHSUNDEEP
mysqlstored-procedures
Data_Manipulator_07
Data_Manipulator_07

Reputation: 55

Error in creating a stored procedure

I'm trying to retrieve values by joining two tables (the customer and enquiry table),then i'm trying to store the retrieved values into another table that would come in handy for reasons irrelevant here.And then i'm finally deleting the retrieved values from the enquiry table. When i'm trying to execute the stored procedure i'm getting the following error shown in the screenshot below. MySQL Error

how do i resolve this error?

Stored Procedure:-

CREATE PROCEDURE `backup_eq`(
    IN `eq` VARCHAR(15), IN `mail` VARCHAR(30), IN `dates` DATE, IN `cmp` VARCHAR(10), IN `rea` VARCHAR(50)) 
NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER 
BEGIN 
SELECT eqno into @eno,Date1 into @d,cmpname into @c,subject into @s,cid into @cd 
FROM `enquiry` NATURAL JOIN `customer` 
WHERE eqno=eq and email=mail and cmpname=cmp and Date=dates; 
INSERT INTO `enquiryBin`(`Eqno`, `Date1`, `Cmpname`, `Subject`, `CID`, `Reason`) 
VALUES (@eno,@d,@c,@s,@cd,rea); 
DELETE FROM `enquiry` 
WHERE eqno=eq and cid=@cd and cmpname=cmp and Date1=dates; 
END

The create table statements of the two tables are given below

CREATE TABLE `customer` (
  `CID` int(15) NOT NULL,
  `Address` varchar(100) NOT NULL,
  `Name` varchar(20) NOT NULL,
  `email` varchar(30) NOT NULL,
  `phone` bigint(20) NOT NULL
)
ALTER TABLE `customer`
  ADD PRIMARY KEY (`CID`);

  CREATE TABLE `enquiry` (
  `Eqno` varchar(15) NOT NULL,
  `Date1` date NOT NULL,
  `Cmpname` varchar(10) NOT NULL,
  `Subject` varchar(100) NOT NULL,
  `CID` int(15) NOT NULL
)

  ALTER TABLE `enquiry`
  ADD PRIMARY KEY (`Eqno`,`Cmpname`,`CID`,`Date1`)

Upvotes: 1

Views: 88

Answers (2)

Barmar
Barmar

Reputation: 780798

There's no need for all those variables, just use an INSERT INTO ... SELECT query, and a JOIN in the DELETE query.

INSERT INTO enquiryBin (`Eqno`, `Date1`, `Cmpname`, `Subject`, `CID`, `Reason`)
SELECT eqno, Date1, cmpname, subject, cid, rea
FROM FROM `enquiry` NATURAL JOIN `customer` 
WHERE eqno=eq and email=mail and cmpname=cmp and Date1 = dates; 

DELETE e FROM enquiry AS e
NATURAL JOIN customer 
WHERE eqno = eq AND email = mail AND cmpname = cmp AND Date1 = dates

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

SELECT eqno into @eno,Date1 into @d,cmpname into @c,subject into @s,cid into @cd

Should be

SELECT eqno, Date1, cmpname, subject, cid INTO @eno, @d, @c, @s, @cd

That is, name all columns in the select-list separately from the INTO clause.

Refer to syntax documentation: https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Upvotes: 2

Related Questions