CODEWITHSUNDEEP
mysqldatabasestored-procedures
Tarounen
Tarounen

Reputation: 1159

MYSQL stored procedure

I am writing a stored procedure on MYSQL to check if there are recording matching some criteria and output values.

I am used to write in MSSQLSEVER

here is an excerpt of the procedure:

CREATE PROCEDURE prc1(
    IN  input  VARCHAR(15),
    OUT output INT
)
this_proc:
BEGIN
    SET output = 0;
    DECLARE inputCount INT DEFAULT 0;

    SELECT COUNT(name) INTO inputCount FROM table WHERE table.name = input;
    IF (inputCount> 0) THEN 
        SET output= 1;
        LEAVE this_proc;
    END IF; 
END;

i am getting errors at each one of this lines:

SET output = 0;
DECLARE ...
IF ...
END IF;
END;

am i doing any syntax error or something?

Upvotes: 0

Views: 115

Answers (1)

Drew
Drew

Reputation: 24970

Give this a shot. It gets past syntax errors, cleans up labels, points you toward fixing mytablename, wraps with delimiters, shows call.

drop procedure if exists prc1;
DELIMITER $$
CREATE PROCEDURE prc1(
    IN  input  VARCHAR(15),
    OUT output INT
)
-- this_proc:
BEGIN

    DECLARE inputCount INT;
    set input=0;
    SET output = 0;


    SELECT COUNT(name) INTO inputCount FROM mytablename WHERE name = input; -- fix mytablename
    IF (inputCount> 0) THEN 
        SET output= 1;
        -- LEAVE this_proc; -- not necessary, you are about to leave anyway !
    END IF; 
END;
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

Test it

call prc1('fred',@myVar);

Delimiters

Delimiters are important to wrap the block of the stored proc creation. The reason is so that mysql understands that the sequence of statements that follow are still part of the stored proc until it reaches the specified delimiter. In the case above, I made up one called $$ that is different from the default delimiter of a semi-colon that we are all used to. This way, when a semi-colon is encountered inside the stored proc during creation, the db engine will just consider it as one the many statements inside of it instead of terminating the stored proc creation. Without doing this delimiter wrapping, one can waste hours trying to create their first stored proc getting Error 1064 Syntax errors. At the end of the create block I merely have a line

$$

which tell mysql that that is the end of my creation block, and then the default delimiter of a semi-colon is set back with the call to

DELIMITER ;

Mysql manual page Using Delimiters with MySqlScript. Not a great manual page imo, but trust me on this one. Same issue when creating Triggers and Events.

Upvotes: 1

Related Questions