Why does dynamic sql not work?

  • 0

    Here is my code which tries to unions all the top 3 salaries in each department by a dynamic sql statement.
    I try it in mysql workbench,it seems ok, but when submitting, it reports internal error.

     set @sql_statement = "",@department_id =0, @loop_index=0;
    select case when @loop_index>0 then @sql_statement := concat(@sql_statement, " union ") else NULL end,
           @sql_statement := concat(@sql_statement, "( select '", department.Name, "' as Department, employee.name as Employee, employee.salary as Salary from employee  where departmentid=", department.Id," order by Salary limit 0,3 )"),
    	   @loop_index :=@loop_index +1
    from department;
    prepare sql_statements from @sql_statement;
    execute sql_statements;

Log in to reply

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.