How do I TryParse in SQL 2000?

Question

I have a stored procedure in an old SQL 2000 database that takes a comment column that is formatted as a varchar and exports it out as a money object. At the time this table structure was setup, it was assumed this would be the only data going into this field. The current procedure functions simply this this:

SELECT CAST(dbo.member_category_assign.coment AS money)
  FROM dbo.member_category_assign
 WHERE member_id = @intMemberId
       AND 
       dbo.member_category_assign.eff_date <= @dtmEndDate
       AND 
       (
        dbo.member_category_assign.term_date >= @dtmBeginDate
        OR 
        dbo.member_category_assign.term_date Is Null
       )

However, data is now being inserted into this column that is not parsable to a money object and is causing the procedure to crash. I am unable to remove the "bad" data (since this is a third party product), but need to update the stored procedure to test for a money parsable entry and return that.

How can I update this procedure so that it will only return the value that is parsable as a money object? Do I create a temporary table and iterate through every item, or is there a more clever way to do this? I'm stuck with legacy SQL 2000 (version 6.0) so using any of the newer functions unfortunately is not available.

Answer 1

Checking for IsNumeric may help you - you can simply return a Zero value. If you want to return a 'N/a' or some other string value

I created the sample below with the columns from your query.

The first query just returns all rows.

The second query returns a MONEY value.

The third one returns a String value with N/A in place of the non-integer value.

set nocount on
drop table #MoneyTest
create table #MoneyTest
(
    MoneyTestId Int Identity (1, 1),
    coment varchar (100),
    member_id int,
    eff_date datetime,
    term_date datetime
)
insert into #MoneyTest (coment, member_id, eff_date, term_date)
values 
    (104, 1, '1/1/2008', '1/1/2009'),
    (200, 1, '1/1/2008', '1/1/2009'),
    (322, 1, '1/1/2008', '1/1/2009'),
    (120, 1, '1/1/2008', '1/1/2009')

insert into #MoneyTest (coment, member_id, eff_date, term_date) 
values  ('XX', 1, '1/1/2008', '1/1/2009')

Select *
FROM #MoneyTest

declare @intMemberId int = 1
declare @dtmBeginDate   DateTime = '1/1/2008'
declare @dtmEndDate DateTime = '1/1/2009'

SELECT 
    CASE WHEN ISNUMERIC (Coment)=1 THEN CAST(#MoneyTest.coment AS money) ELSE cast (0 as money) END MoneyValue
FROM #MoneyTest
WHERE member_id = @intMemberId
AND #MoneyTest.eff_date <= @dtmEndDate
AND 
(
    #MoneyTest.term_date >= @dtmBeginDate
    OR 
    #MoneyTest.term_date Is Null
)

SELECT 
    CASE WHEN ISNUMERIC (Coment)=1 THEN CAST (CAST(#MoneyTest.coment AS money) AS VARCHAR) ELSE 'N/a' END StringValue
FROM #MoneyTest
WHERE member_id = @intMemberId
AND #MoneyTest.eff_date <= @dtmEndDate
AND 
(
    #MoneyTest.term_date >= @dtmBeginDate
    OR 
    #MoneyTest.term_date Is Null
)