CODEWITHSUNDEEP
mysqlstored-procedures
rajprashanth r
rajprashanth r

Reputation: 41

error in my first stored procedure--help!

this is my first stored procedure . i find it very difficult to debug it . help me by spending a little time on this

create procedure myworld.perform_target_proc(
  IN inp_usr_id integer,
  IN inp_tgt_src_id integer,
  IN inp_tgt_src_type varchar(30),
  IN inp_tgt_usr_id integer,
  IN tgt_usr_msg text,
  out tgt_res varchar(30)
)
BEGIN
  declare target_count integer
  select count(target_id) from target where usr_id=inp_usr_id and tgt_src_id=inp_tgt_src_id and tgt_src_type=inp_tgt_src_type
and tgt_usr_id=inp_tgt_usr_id into target_count
  if target_count=0 then
    begin
    insert into target(usr_id, tgt_src_id, tgt_src_type, tgt_usr_id, tgt_usr_msg) values
    (inp_usr_id, inp_tgt_src_id, inp_tgt_src_type, inp_tgt_usr_id, inp_tgt_usr_msg)
    set tgt_res = 'new target created'
    end
  else
    set tgt_res = 'target already exist'
  end if
END |

Upvotes: 0

Views: 58

Answers (2)

Matt MacLean
Matt MacLean

Reputation: 19656

Looks like you are missing some semicolons.

create procedure myworld.perform_target_proc(
  IN inp_usr_id integer,
  IN inp_tgt_src_id integer,
  IN inp_tgt_src_type varchar(30),
  IN inp_tgt_usr_id integer,
  IN tgt_usr_msg text,
  out tgt_res varchar(30)
)
BEGIN
  declare target_count integer;
  select count(target_id) from target where usr_id=inp_usr_id and tgt_src_id=inp_tgt_src_id and tgt_src_type=inp_tgt_src_type
and tgt_usr_id=inp_tgt_usr_id into target_count;
  if target_count=0 then
    insert into target(usr_id, tgt_src_id, tgt_src_type, tgt_usr_id, tgt_usr_msg) values
    (inp_usr_id, inp_tgt_src_id, inp_tgt_src_type, inp_tgt_usr_id, inp_tgt_usr_msg)
    set tgt_res = 'new target created';
  else
    set tgt_res = 'target already exist';
  end if;
END |

Upvotes: 2

Craig Eddy
Craig Eddy

Reputation: 969

I'm a SQL Server guy, not a MySQL guy, but in SQL Server this wouldn't even compile: variables need @ prefix for one thing. My guess is you want 

set @target_count = SELECT count(target_id)....

or better yet avoid the local variable altogether.

Upvotes: 0

Related Questions