For the sake of fun:

You might wish to convert to other integer data types, not only int.

Try to use different types for @tgt in this script:

Declare @hex VARCHAR(50), @tgt TINYINT, @typeLength TINYINT

SET @tgt = 0

SELECT @typeLength = DATALENGTH(@tgt)

Select @hex = 'ff' --9FF000' --Retrieve hex string from a table

Select @hex = '0x' + REPLICATE('0', @typeLength*2-LEN(LTRIM(@hex))) + LTRIM(@hex) --Retrieve hex string from a table

set @tgt = substring([sys].[fn_cdc_hexstrtobin](@hex), 1, @typeLength)

SELECT @tgt, [sys].[fn_cdc_hexstrtobin](@hex)