how to fix User Defined Function scalar value performance issue in sql server 2005? -

user defined function used in stored procedure. user defined function returns scalar value. following logic used in user defined function

create  function [dbo].[udf_test] ( @code varchar(10),  )     returns bit     begin    declare @bflag bit      select @bflag = count(id)       tbl1      code = @code      if  @bflag = 0      begin       select @bflag = count(id)         tbl2      ltrim(rtrim(code)) = @code     end      if @bflag = 0      begin       select @bflag = count(id)         tbl3        ltrim(rtrim(code)) = @code     end      if @bflag = 0      begin       select @bflag = count(id)         tbl4      ltrim(rtrim(code)) = @code     end  return @bflag  

which correct way improve performance in above user defined function?

the best way of improving performance ditch udf entirely.

one immediate thing occurs me

ltrim(rtrim(code)) = @code 

is not sargable each call (i.e. every row returned in outer query) lead 4 table scans.

if replace logic in udf case expression inline in query may much superior execution plan. if can not make predicate sargable @ least allow optimiser explore different join strategies such hash join rather forcing repeated scans of same tables.