Is there any kind of IFERROR in SQL Server?

Asked

Viewed 573 times

0

Assuming the following script:

DECLARE @var1 VARCHAR(100) = 'Stackoverflow';
DECLARE @var2 VARCHAR(100) = '10';
SELECT 1 * @var1;
SELECT 1 * @var2;

If you execute the first select, we will receive an error of:

Conversion failed when Converting the varchar value 'Stackoverflow' to data type int.

If we execute the second select, we will succeed. My question is if there is any kind of treatment - similar to Excel’s SEERRO - to use in the first select, with that we would have something of the following result:

SELECT IFERROR(1 * @var1, 'Erro', 'Sucesso')

Is there something similar to this in SQL Server? I searched for some possibilities, but I’ve always seen about rollback and transaction, my doubt is for some specific function.

sql sql-server

  • https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-2017

  • Although it is not good practice to use global variables, pq vc does not use https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-2017

  • or simply use TRY CATCH https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

1 answer

2


Good morning D. Watson.

In this example that you have presented, the implicit conversion between an INT and a VARCHAR is taking place. In this scenario, SQL Server applies its default rules

https://docs.microsoft.com/pt-br/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

When an operator combines two expressions with data types different, the type of data with the lowest precedence will be converted to the highest precedence data type. If the conversion does not is an implicit conversion with support, an error will be returned.

DECLARE @var1 VARCHAR(100) = 'Stackoverflow';
DECLARE @var2 VARCHAR(100) = '10';
SELECT 1 * @var1;
SELECT 1 * @var2;

In this case the lowest precedence is the VARCHAR which will be converted to INT.

That’s why the message:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'Stackoverflow' to data type int.

Below the two ways to predict this type of error, as was very well explained by JEAN in the links above.

Use the TRY CATCH feature

DECLARE @var1 VARCHAR(100) = 'Stackoverflow';
DECLARE @var2 VARCHAR(100) = '10';

BEGIN TRY 
   SELECT 1 * @var1;
   SELECT 1 * @var2;
   SELECT 'SUCESSO'
END TRY 
/*
*/
BEGIN CATCH 
    SELECT 'ERRO'
END CATCH

CASE WITH ISNUMERIC 

DECLARE @var1 VARCHAR(100) = 'Stackoverflow';
DECLARE @var2 VARCHAR(100) = '10';

SELECT CASE WHEN ISNUMERIC(@VAR1) = 0 THEN 'ERRO' ELSE 'SUCESSO' END

I hope it helps!!

Good luck .

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.