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

Question

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

Move SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; after the DECLAREs. — Darwin von Corax
– Darwin von Corax, Commented Jul 1, 2016 at 0:41

spencer7593 · Accepted Answer · 2016-07-01 01:22:32Z

1

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.

edited Jul 1, 2016 at 1:22

answered Jul 1, 2016 at 0:42

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

1 Comment

HanTael Over a year ago

Thank you for your answer!! Good day ^^

Collectives™ on Stack Overflow

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

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