CODEWITHSUNDEEP
mysqlvariablesstored-procedures
Shah
Shah

Reputation: 45

MySQL stored procedure variable issue

Variable never got updated with query, and always have default values

BEGIN
    DECLARE sP INT DEFAULT 1;
    DECLARE cBB INT DEFAULT 0;

    SELECT sprice, cb INTO  sP, cBB FROM item WHERE id = 2;
END

Need a little guidance here for whats wrong with it (MySQL version 5.5.20)

Upvotes: 0

Views: 138

Answers (2)

Shah
Shah

Reputation: 45

The following two methods works well and no need to DECLARE variable

BEGIN
     SELECT @sP := sprice, @cBB := cb FROM item WHERE id = 2;
END

Or if we want to do it with INTO clause

BEGIN
     SELECT sprice, cb INTO  @sP, @cBB FROM item WHERE id = 2;
END

Difference is that in first statement you can have columns which don't need to be stored in variable while in second one ... INTO ... every listed column must be stored in variable.

Upvotes: 0

Devart
Devart

Reputation: 121922

Rename the variable cb to another one - 

BEGIN
    DECLARE sP INT DEFAULT 1;
    DECLARE cB_var INT DEFAULT 0;

    SELECT sprice, cb INTO  sP, cB_var FROM item WHERE id = 2;
END

Variable names should differ from field names.

Upvotes: 1

Related Questions