Using TRY with CAST? - 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 2005 Forums

Transact-SQL (2005)

Using TRY with CAST?

Author

Topic

Shanew
Starting Member

20 Posts

Posted - 2009-06-04 : 17:25:33

Hello,
Is there away to use TRY so that if the value in a filed is truly NVARCHAR and im using CAST as Money to SUM them it will then just skip that filed/record?

Maybe something like this

SELECT
xx1,
xx2,

BEGIN TRY
SUM(CAST(DX1 AS money))as SumOfDX1,
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
FROM MYTBL

-MYTBL-
XX1 – XX2 – DX1
Bob – Long – 10.50
Shane – Webber– 12.09
Tom – Becvar – 8E.5W (SKIP THIS FILED)
Bill – Gates – 100.32

if not using TRY then how could i do this?
Thanks for any help!!!
Shane

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 17:31:02

No!
Try to omit in WHERE...

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-04 : 17:34:09

Maybe this will help get you going. I'm sure there are other better ways, I jsut did a quick hack as an example:

DECLARE @Foo TABLE (Val VARCHAR(50))

INSERT @Foo
SELECT '10.9'
UNION ALL SELECT '6.12'
UNION ALL SELECT '6.12W'
UNION ALL SELECT '9.08'
UNION ALL SELECT '6'

SELECT *
FROM @Foo
WHERE REPLACE(Val, '.', '') NOT LIKE '%[^0-9]%'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-04 : 17:36:01

Or this - although this is not fool-proof either:


create table #t (m nvarchar(200))
insert #t
select '1.23' union all
select 'DX1' union all
select '12.09' union all
select '8E.5W' union all
select '100.32'

select convert(money, m)
from   (
       select m from #t where isNumeric(m) = 1
       ) d

drop table #t

OUTPUT:
---------------------
1.23
12.09
100.32

Be One with the Optimizer
TG

Shanew
Starting Member

20 Posts

Posted - 2009-06-04 : 20:18:59

Hi and thanks for the help!!

Here is what I ended up doing..

Sum(case when isnumeric(DX1) = 1 then CAST(DX1 AS money)
else convert(money,0)
end) as DX1

This works but as i think more about it I do this in the "WHERE" would be best as stated by webfred.

Thanks for all the help!
Shane

Shane Weddle
www.TechKnowPros.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources