DECLARE @table1 TABLE (campo1 INT)

INSERT INTO @table1 (campo1)
VALUES (1)
, (3)
, (333)
, (334)
, (335);

DECLARE @table2 TABLE (
campo2 VARCHAR(10)
, descripcion VARCHAR(10)
)

INSERT INTO @table2 (
campo2
, descripcion
)
VALUES (
'1'
, 'a'
)
, (
'2'
, 'b'
)
, (
'333'
, 'c'
)
, (
'335'
, 'd'
)
, (
'3329'
, 'f'
)
, (
'a'
, 'e'
)
, (
'No número'
, 'g'
);

CREATE FUNCTION fnCastInt (@columna VARCHAR(max))
RETURNS INT
AS
BEGIN
DECLARE @return INT;
DECLARE @Left VARCHAR(10) = '000000000' + @columna -- Rellenamos con 0 si no es un dato máximo

SELECT @return = CAST(@columna AS INT)
WHERE RIGHT(@Left, 10) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

RETURN @return
END

CREATE FUNCTION TryConvertInt (@value NVARCHAR(4000))
RETURNS INT
AS
BEGIN
RETURN (
SELECT CONVERT(INT, CASE
WHEN LEN(@value) <= 11
THEN CASE
WHEN @value NOT LIKE N'%[^-0-9]%'
THEN CASE
WHEN CONVERT(BIGINT, @value) BETWEEN - 2147483648 AND 2147483647
THEN @value
END
END
END)
);
END

CREATE FUNCTION TryCastInt (@value NVARCHAR(4000))
RETURNS INT
AS
BEGIN
DECLARE @result INT

IF ISNUMERIC(@value) = 1
AND CHARINDEX('.', @value) = 0
AND CHARINDEX('.', @value) = 0
AND CHARINDEX('e', @value) = 0
AND CHARINDEX('E', @value) = 0
SET @result = CAST(@value AS INT)

RETURN @result;
END