SQL Server safely cast string and fail silently

Question

declare @input varchar(255) = 'abc'

select * from table where id = CAST(@input as int)

Can I do this so that the cast will fail silently, or default to some user-provided (or system default) value?

Answer 1

From SQL Server 2012 and up you can use try_convert and try_parse functions.

Until then you can use

DECLARE @input VARCHAR(11) = 'abc'

SELECT * 
FROM table 
WHERE id =  CAST(CASE WHEN @input NOT LIKE '%[^0-9]%' THEN @input END AS INT)

You may need to tweak the test a bit (e.g. it disallows negative integers and allows positive ones bigger than the maximum int) but if you find a suitable test (e.g. the one here) and use a CASE statement you should avoid any casting errors as order of evaluation for CASE is mostly guaranteed.