如何在SQL 2000中使用TryParse? [英] How do I TryParse in SQL 2000?

问题描述

我在一个旧的SQL 2000数据库中有一个存储过程，该存储过程采用一个注释列，该注释列的格式为varchar，并将其导出为money对象.在设置此表结构时，假定这是进入该字段的唯一数据.当前过程的功能很简单:

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.

如何更新此过程，使其仅返回可解析为金钱对象的值?我是创建一个临时表并遍历每个项目，还是有一个更聪明的方法来做到这一点?我受制于旧版SQL 2000(6.0版)，因此不幸的是，无法使用任何较新的功能.

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.

推荐答案

检查IsNumeric可能会帮助您-您只需返回零值即可.如果要返回"N/a"或其他字符串值

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.

第二个查询返回MONEY值.

The second query returns a MONEY value.

第三个返回N/A的String值代替非整数值.

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
)

这篇关于如何在SQL 2000中使用TryParse?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！