Feedback on procedure

[Bart Pietercil]

Hi Ruslan, List,

looking for feedback on this first attempt at putting some real  
business logic in a stored procedure

This is the procedure

create or replace procedure  
php_cm_insert_department_for_organisation(IN departmentName  
VARCHAR(100),OrganisationID Long)
BEGIN
/* check 1: does the OrganisationID exists and is it for an  
organisationtype of 'organisation' = 1 (only type permitted)*/
	DECLARE organisationType integer;
	declare cur1 cursor for
	Select tbl_organisations.objptr_orgtype->listvalue as 'OrgType'
	from tbl_organisations where recid = OrganisationID;

	open cur1;
	fetch first cur1 into organisationType;
	
	if (organisationType <> 1) then
		raise 50001;
	else
		/* now we are sure organisation exists and is of correct type check  
if usergroups exists */
		declare myCounter integer;
		declare ListID integer;
		declare cur2 cursor For Select count(recid) from tbl_typelists where  
objptr_organisation = OrganisationID and typelistname ='usergroups';			
			
		OPEN cur2;
		fetch first cur2 into myCounter;
		close cur2;
		if myCounter = 0 then
			/*no usergroups for this id so create one*/
			insert into tbl_typelists(typelistname,objptr_organisation) values  
('usergroups',OrganisationID);
			ListID = Select Last_RecID();
		else
		/*--there is an existing list select the id*/
			declare cur3 CURSOR FOR Select recid from tbl_typelists where  
objptr_organisation = OrganisationID and typelistname ='usergroups';
			fetch first cur3 into ListID;
		end IF
		/*--insert department*/
		INSERT INTO tbl_typelistvalues(objptr_typelist,displayname)  
values(ListID,departmentName);
	end IF
	exception
	WHEN ERR_CURSOR_WRONG_POSITION then	
	close cur1;
	raise 50002;
	return;
end;


The 50002 error will state 'Non existing Organisation ID', the 50001 '  
Departments only can be attached to Organisations of type  
'organisation'.

Some things I am looking for inspiration :

1) How can I check for an empty cursor WITHOUT triggering an  
exception ? Doing a select (count) or is there a better way ? There  
should be because now I need to do 2 selects.....



tia

Bart