CODEWITHSUNDEEP
mysqlsqlstored-proceduresstored-functions
Eikichi Onizuka
Eikichi Onizuka

Reputation: 151

Using a custom(user-defined) function inside a procedure in mysql

In the code below, there's a function that generates a random string, and a procedure that is supposed to insert random strings into a table called Users. I have successfully created the function and it's working without any problem, but when I try to create the procedure, it returns a syntax error at line "SET @str = SELECT randstring(8);" I think I am trying to call my function in a wrong way. I'm new to databases so please bear with me.

DELIMITER $$

CREATE FUNCTION `Randstring`(LENGTH SMALLINT(3)) RETURNS VARCHAR(100) CHARSET utf8
BEGIN
    SET @returnStr='';
    SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    SET @i = 0;

    WHILE (@i < LENGTH) DO
        SET @returnStr = CONCAT(@returnStr, SUBSTRING(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END

DELIMITER $$
CREATE PROCEDURE insertdata()
BEGIN
    SET @str='';
    DECLARE i INT DEFAULT 0;
    WHILE i <= 1000 DO
        SET @str = SELECT randstring(8);
        INSERT INTO Users (user_name) 
        VALUES(@str);
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

Upvotes: 0

Views: 756

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Presumably, you intend either:

SET @str = randstring(8);

Or:

SELECT @str := randstring(8);

Or:

SET @str = (SELECT @randstring(8));

A SELECT when used as a subquery needs a parentheses before it. However, no subquery is really necessary.

Upvotes: 1

Related Questions