Jürgen Bayer Jürgen Bayer - 16 days ago 9
SQL Question

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) = NULL, @ErrorCode, CAST(CAST(@ErrorCode AS int) AS nvarchar))


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) = 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)?

Answer

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 he point of casting to int and then back to nvarchar.

Comments