-- In SQL6 & above. This requires a table of numbers.
-- There are more examples using the numbers table under the
-- SQL6xScripts in my website. The structure of the table is
-- CREATE TABLE Numbers( number int ) 
-- where number is between 1 and 255 or so.
declare @t varchar (8)
select @t = 'FFFFFFFF'
select sum(
      case lower( substring( hexstr , number , 1 ) )
	when '0' then 0
	when '1' then 1
	when '2' then 2
	when '3' then 3
	when '4' then 4
	when '5' then 5
	when '6' then 6
	when '7' then 7
	when '8' then 8
	when '9' then 9
	when 'a' then 10
	when 'b' then 11
	when 'c' then 12
	when 'd' then 13
	when 'e' then 14
	when 'f' then 15
      end *  convert( decimal( 28 , 0 ) , power( 16 , number - 1 ) )
)
from 
(
select reverse( @t  ) as hexstr
) as h1
join
Numbers n
on number between 1 and len( hexstr )
go

-- In SQL6x & above
declare @hexstr varchar( 10 ) , @intval int
select @hexstr = '0x2A'
exec('declare intcur cursor for select  convert( int , ' + @hexstr + ' )')
open intcur
fetch intcur into @intval
deallocate intcur
select @intval
go

-- In SQL70
declare @hexstr varchar( 10 ) , @intval int , @cmdstr nvarchar( 255 )
select @hexstr = '0x2A'
Select @cmdstr = N'Select @intval = convert( int , ' + @hexstr + ' )'
Exec sp_ExecuteSql @cmdstr, N'@intval Int Out', @intval Out
select @intval
go