I am bashing my head against how to solve this puzzle. Is there any super SQL expert out there who can lend some helpI have a database with the following structure.adjlevel | scheme | holder | client | companyID | RateA rate can be held in this table based on either of the following keyslevel  + companyId + scheme + holder + client OR level  + companyId + scheme + holderOR level  + companyId + scheme OR level  + companyIdThere 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)ASBEGINDECLARE @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 counterWHILE @counter <= 9BEGINSELECT @retval = bhrlyrate FROM [MYDATABASE].dbo.inv_hrrate WHERE adjlevel = @nadjlevel and cmode = @cmodeand companyid = @ncompanyid and client =  case when @level <= 3 THEN @client ELSE '' ENDand scheme = case when @level <= 2  THEN @scheme ELSE '' ENDand holder = case when @level <= 1 THEN @holder ELSE '' ENDand dworkedfrom <= case when @date_done = '30 december 1899' then dworkedfrom ELSE    @date_done ENDand dworkedto >= case when @date_done = '30 december 1899' then dworkedto ELSE @date_done ENDIF @@rowcount > 0  -- Break if record found BREAK;IF @level = 4  -- T search mode unsuccessful change to A mode,-- Reassign a few variables --- and continue searchingBEGIN SET @cmode = 'A'SET @level = 0set @date_done = '30 december 1899'ENDS ET @counter  = @counter + 1SET @level = @level + 1END -- If record is not in hourly rate table then pick default rate from company IF @counter = 8 and @retval = 0SELECT @retval = CO.hrly_rate FROM company CO WHERE companyid = @ncompanyid;RETURN @retvalEND
SQL Query based on results of preceding query
sql
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 = @cmodeand 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 <= case when @date_done = '30 december 1899' then dworkedfrom ELSE    @date_done ENDand dworkedto >= case when @date_done = '30 december 1899' then dworkedto ELSE @date_done ENDORDER BY scheme DESC, holder DESC, client DESC That would eliminate some of the looping and requerying you are doing.   