SQL Server safely cast string and fail silently

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 d开发者_JS百科efault) value?

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.

You can put your select in TRY-CATCH block and re-issue the query in CATCH with default value.