SQL Query based on results of preceding query
I would pull all the possible rates and just sort by the most specific.

SELECT @retval = bhrlyrate FROM [MYDATABASE].dbo.inv_hrrate WHERE 
adjlevel = @nadjlevel 
and cmode = @cmode
and companyid = @ncompanyid 
and (client = @client or client = '')
and (holder = @holder OR (holder = '' AND client = ''))
and (scheme = @scheme or (scheme = '' AND holder = '' AND client = ''))
and dworkedfrom &lt;= case when @date_done = '30 december 1899' then dworkedfrom ELSE    @date_done END
and dworkedto &gt;= case when @date_done = '30 december 1899' then dworkedto ELSE @date_done END
ORDER BY scheme DESC, holder DESC, client DESC 


That would eliminate some of the looping and requerying you are doing.