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

Question

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 ;

Gordon Linoff · Accepted Answer · 2021-05-16 11:22:43Z

1

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.

answered May 16, 2021 at 11:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Eikichi Onizuka Over a year ago

Ty sir, I read somewhere that you use the keyword "CALL" to use procedures and "SELECT" to use functions, so that's why wrote "SELECT" there. I shouldn't have used it here.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related