Is there an ISERROR equivalent in SS?

Question

Post reply

Is there an ISERROR equivalent in SS?

water490

Ten Centuries

Points: 1076
More actions
May 24, 2025 at 4:04 am

#4596226
Hi everyone
I have a SP that just failed. Format of SP is:
```
Select   symbol,
         date,
         calc1,
         calc2
from   sub-query
```
It is due to bad data so I need to update the SP so it handles the scenario better. I am proposing the following logic:
```
CASE WHEN "calc1 has error" THEN NULL ELSE calc1 END
```
The part I am struggling with is "calc1 has error". There could be multiple reasons why calc1 has an error. It doesn't really matter what the error is. As long as calc1 has an error at run time then output NULL else run calc1. In Excel there is an "ISERROR" function that handles this type of scenario but I cannot find anything equivalent in SS (perhaps I overlooked it). How do I code "calc1 has error"? The above code is part of a larger code where results results are passed up from sub-query to parent query so knowing when a sub-query produces a NULL is helpful to the parent because the parent will make appropriate adjustments.
Hopefully what I am saying makes sense. If not let me know and I will gladly clarify.
Thank you
- This topic was modified 2 weeks, 6 days ago by water490.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

water490 Ten Centuries Points: 1076 More actions · Answer 1

I gave the proposed logic CASE but I am open to anything. If code has an error then return NULL else run the code

pietlinden SSC Guru Points: 63578 More actions · Answer 2

Try/Catch blocks?

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver16

water490 Ten Centuries Points: 1076 More actions · Answer 3

pietlinden wrote:

Try/Catch blocks? https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver16

I clarified my original post. Not sure if you saw it or not when you replied.

I did think about TRY/CATCH but not really sure how I can output NULL if there is an error. How would I use TRY/CATCH for my particular scenario?

Steve Collins SSCrazy Points: 2214 More actions · Answer 4

water490 wrote:

How do I code "calc1 has error"? The above code is part of a larger code where results results are passed up from sub-query to parent query so knowing when a sub-query produces a NULL is helpful to the parent because the parent will make appropriate adjustments.

A runtime error halts execution of a query. Pretty sure there's no way around that. TRY/CATCH will divert the flow of control to an additional code but not on a row-by-row basis. It's all or nothing. To get the sub-query to produce a NULL instead of a runtime exception there's the NULLIF function. Or if the error is caused by a type conversion there's TRY_CAST and TRY_CONVERT

declare @t  table(x int, y int);
insert into @t (x, y) values (1, 0), (6, 3);

begin try
    select 'no', x/y from @t;
end try
begin catch
    select 'yes', x/nullif(y, 0) from @t;
end catch

The 'Message' output for this code is as follows

    (2 row(s) affected)
    

    (0 row(s) affected)
    

    (2 row(s) affected)

2 rows were inserted into the temp table, 0 rows were affected by the 'no' query, and then the addition code in the CATCH returns 2 rows with NULL replacing 0 in the denominator to avoid divide by zero errors

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können