SQL Query based on results of preceding query I am bashing my head against how to solve this puzzle. Is there any super SQL expert out there who can lend some help

I have a database with the following structure.

adjlevel | scheme | holder | client | companyID | Rate


A rate can be held in this table based on either of the following keys

level  + companyId + scheme + holder + client 


OR 

level  + companyId + scheme + holder


OR 

level  + companyId + scheme 


OR 

level  + companyId


There will always be one record per level. Therefore I need to write a query that looks at all the above criteria starting with the most filters first and eliminating one criteria at a time until I find the required record.

I have written a function to do the above but it is very slow.

ALTER FUNCTION [dbo].[Hourly_Rate]
(
-- Add the parameters for the function here
@ncompanyid numeric(2),
@client varchar(100),
@scheme varchar(100),
@holder varchar(100),
@nadjlevel numeric(3),
@date_done datetime
)
RETURNS Numeric(5)
AS
BEGIN
DECLARE @cmode varchar(1),
@level numeric(1),
@counter numeric(1),
@retval Numeric(5)

set @cmode = 'T'
set @level = 1  -- @level controls which filter to apply 
set @counter = 1 -- @counter = loop counter

WHILE @counter &lt;= 9
BEGIN

SELECT @retval = bhrlyrate FROM [MYDATABASE].dbo.inv_hrrate WHERE 
adjlevel = @nadjlevel 
and cmode = @cmode
and companyid = @ncompanyid 
and client =  case when @level &lt;= 3 THEN @client ELSE '' END
and scheme = case when @level &lt;= 2  THEN @scheme ELSE '' END
and holder = case when @level &lt;= 1 THEN @holder ELSE '' END
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

IF @@rowcount &gt; 0  -- Break if record found 
BREAK;


IF @level = 4  
-- T search mode unsuccessful change to A mode,
-- Reassign a few variables 
--- and continue searching
BEGIN 
SET @cmode = 'A'
SET @level = 0
set @date_done = '30 december 1899'
END

S ET @counter  = @counter + 1
SET @level = @level + 1

END 

-- If record is not in hourly rate table then pick default rate from company 

IF @counter = 8 and @retval = 0
SELECT @retval = CO.hrly_rate FROM company CO WHERE companyid = 
@ncompanyid;

RETURN @retval

END
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.