CODEWITHSUNDEEP
mysqlstored-proceduresmariadb
HanTael
HanTael

Reputation: 97

how to declare variables(local, user defined) in mysql stored-procedure?

I'm creating stored procedure in mysql. I declare some variables, but occur error. I don't know problem.

Error is

"Incorrect synstax near 'Declare'".

CREATE PROCEDURE SP_APPDOCLISTSIGNBOXREAD_GET(
P_SESSIONID         VARCHAR(256),
P_EMPID             VARCHAR(20),
P_FORMSEQ           VARCHAR(5)
)
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE V_DRAFTDEPT  VARCHAR(20); -- ***This line is error.***
    DECLARE V_ADMINCOUNT INT;

    SET @V_STRSQL = '';         -- Dynamic query variable
    SET @V_STRWHERE = '';       -- Dynamic query variable   
    SET @V_STRPARAM = '';       -- Dynamic query variable

    SET V_DRAFTDEPT = '';
    SET V_ADMINCOUNT = 0;

    SELECT DEPTCODE INTO V_DRAFTDEPT
      FROM TBEMPLOYEE
     WHERE EMPID = P_EMPID;
END

Upvotes: 1

Views: 916

Answers (1)

spencer7593
spencer7593

Reputation: 108400

The order of statements with a MySQL program is pretty specific.

The DECLARE statements have to come first.

And there's an order for the DECLARE statements too. For example, any DECLARE ... HANDLER statements must come after variable declarations.

If it wasn't clear... MySQL is returning an error for the DECLARE statement because it's not valid following a SET statement.

Upvotes: 1

Related Questions