Row-Level Error Handling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Row-Level Error Handling

Author

Topic

slserra
Starting Member

19 Posts

Posted - 2005-06-21 : 13:35:51

When I do a select against a table with a computed column and one of the computations results in an error the query stops executing. Is there a way to have the query continue to execute and have the 'error' computation display as null?

For example, I have the following table of strings:

strDate
--------
6/20/2005
6/21/2005
6/22
6/23/2005

I add a computed column as 'cast([strDate] as datetime) as calcDate'

when I select all, I would like the results to show:

strDate calcDate
-------- --------
6/20/2005 6/20/2005
6/21/2005 6/21/2005
6/22 null
6/23/2005 6/23/2005

without any error or warning messages.

I see that I can use SET ARITHABORT and SET ARITHIGNORE to handle divide by 0 situations but these settings do not seem to have any impact on other types of errors.

Any help is appreciated.

Steve

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-21 : 13:46:09

No - I don't think so.
but you can handle it in your code:

create table #test(strDate varchar(10))
insert into #test
select '6/20/2005' union
select '6/21/2005' union
select '6/22' union
select '6/23/2005'

select
strDate,
case when isdate(strdate) = 1 then cast(strdate as datetime)
else null end
from #test

drop table #test

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources