Syntax Error in MySQL stored procedure

Question

The below SP is not giving any result even though there are 48 rows as per the where clause

BEGIN
DECLARE SelectClause VARCHAR(2000);
  if v_mode='SearchByString' then
    SET SelectClause ='select SURVEY_USER.username,SURVEY.* from SURVEY, SURVEY_USER';

if v_SearchString is not null then
    SET SelectClause=CONCAT(@SelectClause,'  where ');
    Set SelectClause=CONCAT(@SelectClause,v_SearchString);
end if;
SET SelectClause=CONCAT(@SelectClause,'  order by SURVEY.created_date DESC;') ;
select SelectClause;
SET @query = SelectClause;
    PREPARE stmt FROM  @query;
    EXECUTE stmt;
 select stmt;
end if;
END

I tried a lot but not getting any problem. I also tried select clause to print the command at various places to not able to print it. Please give me some solution. There are my parameters that I am passing v_mode='SearhByString' v_SearchString='SURVEY_USER.username=chiragfanse'

It should return 48 rows but does not return anything.

Including the error message might be helpful.

mu is too short
– mu is too short

2011-10-18 08:24:38 +00:00
Commented Oct 18, 2011 at 8:24 — mu is too short
– mu is too short, Commented Oct 18, 2011 at 8:24

Devart · Accepted Answer · 2011-10-18 08:42:58Z

2

BEGIN

  DECLARE SelectClause VARCHAR(2000);

  IF v_mode = 'SearchString' THEN

    SET SelectClause = CONCAT('select SURVEY_USER.username,SURVEY.* from SURVEY, SURVEY_USER');

    IF SearchString IS NOT NULL THEN
      SET SelectClause = CONCAT(SelectClause, ' where ', SearchString);
    END IF;

    SET SelectClause = CONCAT(SelectClause, '  order by SURVEY.created_date DESC;');

    SET @query = SelectClause;
    PREPARE stmt FROM  @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END IF;
END

All declaration have to be at the begining.
Rename @SelectClause to SelectClause, because you are declaring this variable.
Check the usage of SET clauses. I have added one.
Have a look at prepared statements reference, it will help you to execute the query you built.

edited Oct 18, 2011 at 8:42

answered Oct 18, 2011 at 8:29

Devart

120k23 gold badges172 silver badges189 bronze badges

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

4 Comments

Microsoft Developer Over a year ago

@Devart.I Wrote the code same as you provided but it shows the error near DECLARE SelectClause VARCHAR(2000)

Devart Over a year ago

I have no problems with this code. Please show full procedure (CREATE PROCEDURE...).

Devart Over a year ago

Note, than SelectClause and @SelectClause are different variables; have a look at my code. Fix it firstly. About the last error - check genarated query, try to output it with SELECT statement.

Microsoft Developer Over a year ago

let us continue this discussion in chat

Nathan · Accepted Answer · 2011-10-18 08:27:55Z

0

you have wrong concat functions. Try this.

if v_mode='SearchString' then
DECLARE @SelectClause varchar(2000);
SET @SelectClause =CONCAT(select (SURVEY_USER.username,SURVEY.*) from SURVEY, 'SURVEY_USER');
if SearchString is not null then
@SelectClause=CONCAT(@SelectClause, 'where' ,SearchString);
end if;
SET @SelectClause=@SelectClause
order by SURVEY.created_date DESC
execute(@SelectClause)
end if;

try this. let me know if you need anything else.

answered Oct 18, 2011 at 8:27

Nathan

1,5201 gold badge12 silver badges22 bronze badges

Collectives™ on Stack Overflow

Syntax Error in MySQL stored procedure

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related