below a function I have been using in a App. Its quite similar to yours. Its fast as it only loops in memory and does not require temp tables. as usual usage examples are in the remarks on the top of the function. The input string size is 4000 characters, therefore it can only return an ascii string of 2000 characters ie 1/2 the size passed in. secondly, if the string passed in is of an odd length, it left pads a 0 before iterating so that it does not crash

create function Hex2String(@strHex as nvarchar(4000))

returns nvarchar(2000)

as

begin

 /*

  Function  : Hex2String

  Author     : Pratap J Prabhu

  Syntax    :

   >  select dbo.Hex2String('6566676869')

   >  declare @strHexString  nvarchar(4000)

      declare @strReturnedString   nvarchar(4000)

      set @strHexString      ='65666768'

      set @strReturnedString = dbo.Hex2String(@strHexString)

      print @strReturnedString

  Purpose     : passed a hexadecimal encoded string this function returns a Ascii string

  Limitations : Max input Hex string size = 4000 characters

                                  Max string size returned  = 2000

 */

 declare @cChar     char(1)

 declare @nDeciVal  int

 declare @cHexBit1  char(1)

 declare @cHexBit2  char(1)

 declare @strRetVal  nvarchar(2000)

 declare @iLoop   smallint

 set @strRetVal=''

 set @strHex = ltrim(rtrim(isnull(@strHex,'')))

 if @strHex<>''

 begin

  -----   Hex numbers should always have a even length, so lets make it so

  if (round(len(@strHex)/2,0)*2)<>len(@strHex)

   set @strHex='0'+@strhex

  -----   Now loop down the length of the HexString handling 2 bits at a time

  set @iLoop=1

  while @iLoop <len(@strHex)

  begin 

   set @cHexBit1=substring(@strHex, @iLoop  , 1) -- The First Bit

   set @cHexBit2=substring(@strHex, @iLoop+1, 1) -- the second bit

   -- Convert the First Hex Bit value to its equivalent Decimal Value

                        -- Multiplying by 16 as its in the 10s place

   if @cHexBit1>=0 and @cHexBit1<=9

    set @nDeciVal=convert(int,@cHexBit1)*16

   else

    set @nDeciVal=convert(int,ASCII(@cHexBit1)-ASCII('A')+1) * 16

   -- Convert the second Hex Bit value to its equivalent Decimal Value

                        -- do not Multiply by 16 as its in the units place

   if @cHexBit2>=0 and @cHexBit2<=9

    set @nDeciVal=@nDeciVal+convert(int,@cHexBit2)

   else

    set @nDeciVal=@nDeciVal+(ASCII(@cHexBit2)-ASCII('A')+1)

   -- Store the Ascii Value

   set @strRetVal=@strRetVal+ char(@nDeciVal)

   set @iLoop=@iLoop+2 -- continue to the next character ie the next 2 Hex Bits

  end

 end   

 return (@strRetVal)

end

go