SQLTeam.com | Weblogs | Forums

If else stored procedure


#1

Write a stored procedure that accepts one input argument which defines the status of event manager to either ‘Senior’ or ‘Junior’. The input argument states the minimum number of engagements handled by the event manager in order to be a senior. For instance, if 3 is given as the input argument, event managers who have handled at least 2 engagements are promoted to ‘Senior’. Otherwise, the stored procedure should set the status to ‘Junior’.

Above is the question.
.. anyone can help me .Following is my solution
create procedure PRC_Min(IN Min INT)
Begin
IF
((Select COUNT(Staff_id) from Engagement
where Event_manager.Staff_id=Engagement.Staff_id) >= Min)
THEN
Update Event_manager
set Status= 'Senior';
ELSE
Update Event_manager
set Status= 'Junior';
END IF;
END@

Event_manager table just consist of staff info..
Engagement table having all date.. location.. etc

SQL0206N "EVENT_MANAGER.STAFF_ID " is not valid in the context where it is used.


#2

Maybe this:

create procedure PRC_Min(@min int)
as
begin
   update em
      set em.Status=case when isnull(e.c,0)<=2 then 'Junior' else 'Senior' end
     from Event_manager as em
          left outer join (select Staff_id
                                 ,count(*) as c
                             from Engagement
                            group by Staff_id
                          ) as e
                       on e.Staff_id=em.Staff_id
    where (isnull(e.c,0)<=@min
      and  em.Status<>'Junior'
          )
       or (isnull(e.c,0)>@min
      and  em.Status<>'Senior'
          )
    ;
end;

#3

isnull(e.c,0) may i know what is mean by this.. because im just a beginner .. haha


#4

If not entries in Engagement is found, then value of e.c is null. I'm merely turning null to 0