MySQL Forums
Forum List  »  Stored Procedures

Re: Issue with building dynamic SQL statement based on parameters passed to Stored procedure.
Posted by: Peter Brawley
Date: January 25, 2015 01:57PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Issue with building dynamic SQL statement based on parameters passed to Stored procedure.
2034
January 25, 2015 01:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.