Search code examples
sqlsql-serversql-server-2014

SQL Server 2014: How to cast a string value to int only if the value can be casted?

I try to cast a string value in a field of a table to int, but only in the case that the value stores a value that can be casted to int (in the other case the original value must be returned). Example:

   DECLARE @ErrorCode nvarchar(1024)
   SET @ErrorCode = 'a10'
   SELECT IIF(TRY_CAST(@ErrorCode AS int) IS NULL, @ErrorCode, CAST(@ErrorCode AS int))

I've seen similar codes on StackOverflow.

My problem is that the SQL Server (2014) does not seem to short-circuit, and executes the cast always, even if TRY_CAST(@ErrorCode AS int) results in NULL. The result of the code above is the error "Conversion failed when converting the nvarchar value 'a10' to data type int."

See this sample on rextester.com

I also tried other variants with the same result:

SELECT CASE WHEN TRY_CAST(@ErrorCode AS int) IS NULL THEN @ErrorCode ELSE (SELECT CAST(@ErrorCode AS int)) END
SELECT CASE TRY_CAST(@ErrorCode AS int) WHEN 1 THEN CAST(@ErrorCode AS int) ELSE @ErrorCode END

How can I achieve my goal (avoid the cast in case the value in @ErrorCode cannot be casted)?

Solution

  • The simple solution would be to use COALSECE:

    DECLARE @ErrorCode nvarchar(1024)
SET @ErrorCode = 'a10'
SELECT COALSECE(CAST(TRY_CAST(@ErrorCode AS int) as nvarchar(1024)), @ErrorCode)

    However, I don't see the point of casting to int and then back to nvarchar.