Re: Issue with building dynamic SQL statement based on parameters passed to Stored procedure.

[Peter Brawley]

Some problems ...

1. Backslash is an escape character so it's problematic as a delimiter

2. Null is never equal to anything, even itself; test nullity with ...is [not] null...

3. The values are strings so you need to quote them.

4. Testing sql preparation in an sproc is awkward; best practice is to echo the sql string till it's thoroughly debugged, then comment it out for use.

That gives us ...

drop procedure if exists getemployeelist;
delimiter go
create procedure getemployeelist( in p_city varchar(20), in p_state varchar(5), in p_dno int ) 
begin 
  if p_city is null then 
    set @city="dallas"; 
  end if; 
  if p_state is null then 
    set @state="tx"; 
  end if; 
  set @stmt = concat( "select emp.* from employee emp join dept d on emp.dno=d.dno where d.city='", @city, "' and d.state='", @state, "'" ); 
  select @stmt;
  prepare stmt from @stmt; 
  execute stmt; 
  deallocate prepare stmt; 
end;
go
delimiter ;