Nicely done... If I may suggest, however, a method that avoids both loops and dynamic SQL...

--===== If the test table exists, drop it

     IF OBJECT_ID('TempDB..#HexToAlpha','U') IS NOT NULL

        DROP TABLE #HexToAlpha

--===== Supress the auto-display of rowcounts

    SET NOCOUNT ON

--===== This is your original table creation/population code

Create table #HexToAlpha

(

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

 , hexstring char(20)

 , alphastring char(10)

)

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)

--===== Ready to rock... setup a couple of variables

     -- Timer to measure duration

DECLARE @Start DATETIME

    SET @Start = GETDATE()

     -- The "key" to this demo and the speed of execution

DECLARE @Numbers VARCHAR(16)

    SET @Numbers = '0123456789ABCDEF'

--===== Demo the solution for 10 characters

 UPDATE #HexToAlpha

    SET alphastring =

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 1,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString, 2,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 3,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString, 4,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 5,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString, 6,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 7,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString, 8,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 9,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString,10,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,11,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString,12,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,13,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString,14,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,15,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString,16,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,17,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString,18,1),@Numbers,1) - 1)))

      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,19,1),@Numbers,1) - 1)

                            + (CHARINDEX(SUBSTRING(HexString,20,1),@Numbers,1) - 1)))

   FROM #HexToAlpha

--===== Display the duration

  PRINT STR(DATEDIFF(ms,@Start,GETDATE())) + ' Milliseconds duration'

--===== Display the final contents of the test table

 SELECT * FROM #HexToAlpha

If the number of characters becomes unpredictable, the use of a Tally table would make the solution almost as easy with only a minor sacrifice in speed.