how to use DB::unprepared() for mysql function and stored procedure in laravel 5.5 migration

Question

I tried using DB::unprepared() in migration files after reading old questions. but it only gives me "A non-numeric value encountered" error and nothing changes in database. However, when I enter sql statement directly into phpmyadmin by adding delimiter, it works. please suggest me what I am missing.

DB::unprepared('
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName VARCHAR(50),
        sSeqGroup VARCHAR(10)
    ) RETURNS VARCHAR(20)
    BEGIN
        DECLARE nLast_val INT;

        SET nLast_val =  (SELECT seq_val
                              FROM _sequence
                              WHERE seq_name = sSeqName
                                    AND seq_group = sSeqGroup);
        IF nLast_val IS NULL THEN
            SET nLast_val = 1;
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nLast_Val);
        ELSE
            SET nLast_val = nLast_val + 1;
            UPDATE _sequence SET seq_val = nLast_val
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF;

        SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,0)));
        RETURN @ret;
    END//
    ');

Answer 1

Oh lol it's a typo. You forgot to escape the line:

SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,0)));

Which it should be:

SET @ret = (SELECT concat(sSeqGroup,\'-\',lpad(nLast_val,6,0)));

Updated:

DB::unprepared('
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName VARCHAR(50),
        sSeqGroup VARCHAR(10)
    ) RETURNS VARCHAR(20)
    BEGIN
        DECLARE nLast_val INT;

        SET nLast_val =  (SELECT seq_val
                              FROM _sequence
                              WHERE seq_name = sSeqName
                                    AND seq_group = sSeqGroup);
        IF nLast_val IS NULL THEN
            SET nLast_val = 1;
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nLast_Val);
        ELSE
            SET nLast_val = nLast_val + 1;
            UPDATE _sequence SET seq_val = nLast_val
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF;

        SET @ret = (SELECT concat(sSeqGroup,\'-\',lpad(nLast_val,6,0)));
        RETURN @ret;
    END//
');

What happened here is you did not escape the quotes and PHP thinks that you are doing 'long chunk of text' - 'long chunk of text', which of course raise the error A non-numeric value encountered. This warning is telling you that you are trying to do mathematical expression on one or more non numeric string: http://php.net/manual/en/migration71.other-changes.php