U0400006: GaussDB does not support SELECT in a stored procedure

Description

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: all GaussDB versions

Syntax Example

This error is reported because UGO does not convert SELECT in a stored procedure by default.

SELECT cannot be directly used in a stored procedure of a GaussDB database, for example:

CREATE PROCEDURE demo()
AS
    DECLARE total INT;
BEGIN
    SELECT count(1) INTO total FROM emp_t WHERE deptno = 10;
    SELECT total;
END;

Suggestion

Solution 1: Modify Output type select statement in procedure conversion and comment out query statements in the stored procedure. The result set returned by SELECT in a MySQL stored procedure can be obtained by the calling client. If you comment out SELECT, applications calling the stored procedure may be affected.

Solution 2:Use the OUT parameter of the stored procedure to obtain the expected output. The mode of calling a client needs to be modified.

CREATE PROCEDURE demo(total OUT INT)
AS
BEGIN
    SELECT count(1) INTO total FROM emp_t WHERE deptno = 10;
END;

Solution 3: If data needs to be returned for the original stored procedure, you can modify the stored procedure to a stored function or even modify application code to implement the original logic.