USERRR5 USERRR5 - 6 months ago 13
MySQL Question

mysql function is not working properly

My database name is

dfeb
and I have a table named
invdt
and columns named
batch_number
,
id
.I want to get the
batch_number
where id=59, using a function.But an error is showing up
function does not exist
.

DELIMITER $$
CREATE
FUNCTION `dfeb`.`get_quantity`(PID)
RETURNS VARCHAR
BEGIN
DECLARE NAME_FOUND VARCHAR DEFAULT "";
SELECT `batch_number` INTO NAME_FOUND FROM `invdt` WHERE ID = PID;
RETURN NAME_FOUND
END$$

DELIMITER ;

SELECT `get_quantity`(59) FROM `invdt`

Answer

Try:

DELIMITER $$

-- CREATE FUNCTION `dfeb`.`get_quantity`(PID)
CREATE FUNCTION `dfeb`.`get_quantity`(PID INT)
-- RETURNS VARCHAR
RETURNS VARCHAR(200)
BEGIN
  -- DECLARE NAME_FOUND VARCHAR DEFAULT "";
  DECLARE NAME_FOUND VARCHAR(200) DEFAULT "";
  SELECT `batch_number` INTO NAME_FOUND FROM `invdt` WHERE ID = PID;
  -- RETURN NAME_FOUND
  RETURN NAME_FOUND;
END$$

DELIMITER ;

-- SELECT `get_quantity`(59) FROM `invdt`
SELECT `dfeb`.`get_quantity`(59) FROM `invdt`;

UPDATE

CREATE FUNCTION `dfeb`.`get_quantity`(PID INT)
.
.
.

-- SELECT `get_quantity`(59) FROM `invdt`
SELECT `dfeb`.`get_quantity`(59) FROM `invdt`;

Or

USE `dfeb`;

CREATE FUNCTION `get_quantity`(PID INT)
.
.
.

SELECT `get_quantity`(59) FROM `invdt`;
Comments