You are simply returning 1 value instead of a table full of values. Assign the result to a variable of type int. In SQL Server I would do something like the following:

DECLARE @retVal int;

select Set @retVal = agent_id 
     from agent ag
     where site = strSite and
          upper(trim(ag.lname)) = strLastName and
	  upper(trim(ag.fname)) = strFirstName;

return @retVal