mysql stored procedure error in creation

Question

I am new in learning stored procedure.Now I am trying to make a procedure which will generate a random number and store it in a variable and then insert the value in another table.

create procedure getRand() 
begin 
delcare rc varchar(255); 
SELECT CHAR(ROUND(65 + FLOOR( RAND() * 57 ))) into rc; 
insert into Rand_loader (snum,rnum) values (rc,rc);
end//

But when I tried to run this on mysql then it gives this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rc varchar(255); 
SELECT CHAR(ROUND(65 + FLOOR( RAND() * 57 ))) into rc; 
insert' at line 3

I am unable to understand this error.Ill appreciate your help
Please help me to solve this

problem solved by the suggestion given in comment.Yes it is the DECLARE issue — Despicable
– Despicable, Commented Dec 23, 2013 at 15:38

Freak · Accepted Answer · 2013-12-24 10:38:08Z

2

Try below code. It works for me

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `yourdatabasename`.`getRand`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
 DECLARE rc VARCHAR(255); 
SELECT CHAR(ROUND(65 + FLOOR( RAND() * 57 ))) INTO rc; 
INSERT INTO Rand_loader (snum,rnum) VALUES (rc,rc);

    END$$

DELIMITER ;

You can download SQLyog from here

https://code.google.com/p/sqlyog/downloads/list

to create tables and stored procedure with visual tools

edited Dec 24, 2013 at 10:38

Freak

6,9035 gold badges41 silver badges56 bronze badges

answered Dec 23, 2013 at 15:38

A. Zalonis

1,6096 gold badges27 silver badges42 bronze badges

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

Comments

Ecd · Accepted Answer · 2013-12-23 15:30:48Z

1

You have written "declare" wrong.

CREATE PROCEDURE `getRand`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
declare rc varchar(255); 
SELECT CHAR(ROUND(65 + FLOOR( RAND() * 57 ))) into rc; 
insert into Rand_loader (snum,rnum) values (rc,rc);
END

answered Dec 23, 2013 at 15:30

Ecd

4404 silver badges9 bronze badges

Comments

Freak · Accepted Answer · 2013-12-24 10:38:30Z

0

You just need to replace declare with DECLARE and always try to use capital letters while writing SQL commands.

answered Dec 24, 2013 at 10:38

Freak

6,9035 gold badges41 silver badges56 bronze badges

Collectives™ on Stack Overflow

mysql stored procedure error in creation

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related