How Do I Tryparse In Sql 2000?

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 struct

Solution 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 ondroptable #MoneyTest
createtable #MoneyTest
(
    MoneyTestId IntIdentity (1, 1),
    coment varchar (100),
    member_id int,
    eff_date datetime,
    term_date datetime
)
insertinto #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')

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

Select*FROM #MoneyTest

declare@intMemberIdint=1declare@dtmBeginDate   DateTime ='1/1/2008'declare@dtmEndDate DateTime ='1/1/2009'SELECTCASEWHEN ISNUMERIC (Coment)=1THENCAST(#MoneyTest.coment AS money) ELSEcast (0as money) END MoneyValue
FROM #MoneyTest
WHERE member_id =@intMemberIdAND #MoneyTest.eff_date <=@dtmEndDateAND 
(
    #MoneyTest.term_date >=@dtmBeginDateOR 
    #MoneyTest.term_date IsNull
)

SELECTCASEWHEN ISNUMERIC (Coment)=1THENCAST (CAST(#MoneyTest.coment AS money) ASVARCHAR) ELSE'N/a'END StringValue
FROM #MoneyTest
WHERE member_id =@intMemberIdAND #MoneyTest.eff_date <=@dtmEndDateAND 
(
    #MoneyTest.term_date >=@dtmBeginDateOR 
    #MoneyTest.term_date IsNull
)

Solution 2:

Apologies for making a new answer, where a comment would suffice, but I lack the required permissions to do so. Onto the answer to your question, I would only like to add that you should use the above ISNUMERIC carefully. While it works much as expected, it also parses things like '1.3E-2' as a value numeric, which strangely enough you cannot cast into a numeric or money without generating an exception. I generally end up using:

SELECTCASEWHEN ISNUMERIC( some_value ) =1AND CHARINDEX( 'E', Upper( some_value ) ) =0THENCast( some_value as money ) 
         ELSECast( 0as money )
    ENDas money_value