why not use Ryan Price's solution?

No loops, minimal dynamic sql

Create table #HexToAlpha

recordid dec(5,0) identity(1,1)

, hexstring char(20)

, alphastring varchar(20)

)

Insert into #HexToAlpha values('416E6E61737461736961', null)

Insert into #HexToAlpha values('416E6E656C6F75697361', null)

Insert into #HexToAlpha values('416E746F696E65747465', null)

Insert into #HexToAlpha values('4265726E616465747465', null)

Insert into #HexToAlpha values('4265726E617264696E65', null)

Insert into #HexToAlpha values('436872697374656C6C65', null)

Insert into #HexToAlpha values('4368726973746F706572', null)

Insert into #HexToAlpha values('43696E646572656C6C61', null)

Insert into #HexToAlpha values('436C656D656E74696E65', null)

Insert into #HexToAlpha values('4576616E67656C696E65', null)

Insert into #HexToAlpha values('4672616E636973637573', null)

Insert into #HexToAlpha values('467265646572696B7573', null)

Insert into #HexToAlpha values('4777656E646F6C696E65', null)

Insert into #HexToAlpha values('4A61637175656C696E65', null)

Insert into #HexToAlpha values('4B726973746F70686572', null)

Insert into #HexToAlpha values('4D617267756572697461', null)

Insert into #HexToAlpha values('4D617279636C61697265', null)

Insert into #HexToAlpha values('53656261737469616E6F', null)

Insert into #HexToAlpha values('536861756E74656C6C65', null)

Insert into #HexToAlpha values('5768696C68656D696E61', NULL)

DECLARE @SQL NVARCHAR(1000)

create TABLE #tempsql

recordid INT,

sql NVARCHAR(500)

)

SET @SQL = 'insert into #tempsql select recordid,''update #HexToAlpha set alphastring = cast(cast((0x'' + hexstring +'') as varbinary) as varchar)'' from #HexToAlpha'

PRINT @SQL

EXEC sp_executesql @SQL

EXEC sp_execresultset 'select sql + '' where recordid = '' + cast(recordid as nvarchar) from #tempsql'

SELECT * FROM #HexToAlpha

DROP TABLE #HexToAlpha

DROP TABLE #tempsql

select convert(varchar,dbo.hexstring_to_binary('0x' + '416E6E61737461736961'))

Here's the function:

ALTER function [dbo].[hexstring_to_binary]
    (
    @hex_string     varchar(max)
    )
returns varbinary(max)
as
begin
    declare @hex            char(2)
    declare @position       int
    declare @count          int
    declare @binary_value   varbinary(max)
    set @count = len(@hex_string)
    set @binary_value = cast('' as varbinary(1))

    if substring(@hex_string, 1, 2) = '0x'
        set @position = 3
    else
        set @position = 1
    while (@position <= @count)
    begin
        set @hex = substring(@hex_string, @position, 2)
        set @binary_value = @binary_value +
                cast(case when substring(@hex, 1, 1) like '[0-9]'
                    then cast(substring(@hex, 1, 1) as int)
                    else cast(ascii(upper(substring(@hex, 1, 1)))-55 as int)
                end * 16 +
                case when substring(@hex, 2, 1) like '[0-9]'
                    then cast(substring(@hex, 2, 1) as int)
                    else cast(ascii(upper(substring(@hex, 2, 1)))-55 as int)
                end as binary(1))
        set @position = @position + 2
    end
    return @binary_value
end -- hexstring_to_binary

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

Hey, Richard... just to let you know... I can normally beat most looping functions by using a Tally table... but not this one (least ways, not so far ).  Your looping function is about 2 seconds faster that my Tally table solution on a 100,000 row test table.  Nice job!

FYI... the direct fixed width solution I posted smokes both the Tally table and the looping inside the function by a full 10 out of 13 seconds.

Sure thing, JH... I don't like the looks of that unconstrained cross-join in the function, though... I think it's going to gen 65,536 internal rows every time it executes...

I'll try without and with a limit in the criteria...

Ok... I was right about the cross-join... and it took more than 3 minutes to exec on 100,000 rows.

And when I change that snippet in the function to have a limit (see below), it still takes about 46 seconds (Richard's weigh's in at only 13).

select @output = @output + char(b.dec)
from dec2hex a, dec2hex b
where b.hex = substring(@input,a.dec*2+1,2)
AND a.dec*2+1 < LEN(@input)
order by a.dec

I think you're on the right track using a helper table to do this without a loop or dynamic SQL...

In case anyone else want's to do some testing on the problem, here's the code I used to gen the 100,000 row test table...

--===== Create and populate a 100,000 row test table.
 SELECT TOP 100000
        RowNum     = IDENTITY(INT,1,1),
        HexValue   = '416E6E61737461736961'
   INTO dbo.HexTest
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2

--===== Add primary key
  ALTER TABLE dbo.HexTest
        ADD PRIMARY KEY CLUSTERED (RowNum)