Stored procedure variable not returning expected value in MySQL?

Question

I'm debugging this code

create procedure create_view ( IN t varchar(50)) 
BEGIN
  prepare stmt from 'select @cnt= count( weight ) from test where url = ?;';
  execute stmt using @t;
  set @base = @cnt /4;
  set @offset = @cnt / 2;
  set @query = concat('create or replace view view_by_url as select url, weight from test where url = ',@t,' order by weight limit  ',@base,' , ',@offset,' ;');
  select t as 'param';
  select cnt as 'count';
  select @base as 'base';
  select @offset as 'offset';
  select @query as 'query';
 -- prepare stmt from @query;
 -- execute stmt ;
END;
call create_view('a');

And @t returns 'a' in result set but @cnt, @base and @offset don't. And I can't explain myself why. Can you give me some help?

sll · Accepted Answer · 2011-08-14 00:05:27Z

1

Try out single SELECT at the end of stored procedure:

  SELECT 
       t as 'param', 
       @cnt as 'count', 
       @base as 'base', 
       @offset as 'offset', 
       @query as 'query';

answered Aug 14, 2011 at 0:05

sll

62.7k22 gold badges109 silver badges157 bronze badges

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

1 Comment

sll Over a year ago

@munch: where is @cnt declared? I see you are using in inside a dynamic sql query, bu have you declared it before? If not - try out to declare before query

munch · Accepted Answer · 2011-08-15 08:11:50Z

0

The problem seems to be in the SELECT. '=' operator is comparison or something like that while to achieve desired behavior in this case ':=' should be used.

prepare stmt from 'select @cnt= count( weight ) from test where url = ?;';

This change makes the whole piece of code work fine.

answered Aug 15, 2011 at 8:11

munch

2,0932 gold badges17 silver badges24 bronze badges

Collectives™ on Stack Overflow

Stored procedure variable not returning expected value in MySQL?

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