What is wrong with my mysql stored procedure?

Question

I have a stored procedure that checks whether or not a new entry is already existing in the table. if it exists, the insert will not happen. when I run it, there is an error

DROP PROCEDURE IF EXISTS AddPriority2;
DELIMITER $$

CREATE PROCEDURE AddPriority2
(
    IN strName VARCHAR(100),
    OUT itExists INT
)
BEGIN
DECLARE 
SELECT COUNT(Id) INTO itExists
FROM priorities
WHERE Name = strName AND StatId = 1;

IF(itExists = 0) THEN
INSERT INTO priorities
(
    NAME,
    StatId
)
VALUES
(
    strName,
    1
);
END IF;
END

here is the error

Query: CREATE PROCEDURE AddPriority2 ( IN strName VARCHAR(100), OUT itExists INT ) BEGIN DECLARE SELECT COUNT(Id) INTO itExists FROM pr...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT COUNT(Id) INTO itExists
FROM priorities
WHERE Name = strName AND StatId =' at line 8

P.Salmon · Accepted Answer · 2019-09-02 07:13:29Z

2

1) You cannot declare a select statement - a declare has to be for a variable..(and I would not use an output parameter for that) 2) or you can use exists instead

if not exists (select 1 from priorities WHERE Name = strName AND StatId = 1) then
   insert...
end if;

edited Sep 2, 2019 at 7:13

answered Sep 2, 2019 at 7:08

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What is wrong with my mysql stored procedure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related