MySQL stored procedure variable issue

Question

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)

Why not SELECT sP AS sprice, cB AS cb FROM item WHERE id = 2; directly? Why do you need this variables inside this procedure? — Mahmoud Gamal
– Mahmoud Gamal, Commented Mar 12, 2013 at 8:55
variables should also work (maybe he will use them later in another query in procedure). Are you sure SELECT sprice, cb FROM item WHERE id = 2 return any value? — Bojan Kovacevic
– Bojan Kovacevic, Commented Mar 12, 2013 at 8:59
it returns values, even work well if i use INTO @varname but some issue with the above mentioned way — Shah
– Shah, Commented Mar 12, 2013 at 11:02
If names are different from db fields (like Devart suggested) then something with fetching query is wrong. Can you setup fiddle — Bojan Kovacevic
– Bojan Kovacevic, Commented Mar 12, 2013 at 11:17
sqlfiddle.com/#!2/85567 i didnt get how to add my procedure in there. hope you got the idea from the above post — Shah
– Shah, Commented Mar 12, 2013 at 12:07

Devart · Accepted Answer · 2013-03-12 09:18:40Z

1

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.

answered Mar 12, 2013 at 9:18

Devart

120k23 gold badges172 silver badges189 bronze badges

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

1 Comment

Shah Over a year ago

Var names are symbolic but anyway i changed it in the main post to avoid confusion thanks for your suggestion

Shah · Accepted Answer · 2013-03-13 05:44:13Z

0

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.

answered Mar 13, 2013 at 5:44

Shah

455 bronze badges

Collectives™ on Stack Overflow

MySQL stored procedure variable issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related