需要有关测试 ISNUMERIC 语法的帮助，以防万一

Question

我试图停止输入负值以在任何范围内进行处理，而不是如果它们是负值，则它们应该下降到第一个 case 语句的末尾作为“无效”。这不起作用，因为当我对 (-1000) 的输入运行测试时，我得到一行 <=50K。我担心我的语法是错误的，但不知道为什么。

ALTER FUNCTION [dbo].[FN_1ST_UPB_Bands]
(
    @FN_1ST_UPB_Band            int
     )

RETURNS varchar(16)
AS
BEGIN

declare @Return varchar (16)

select @Return =

        Case
           When ISNUMERIC(@FN_1ST_UPB_Band)= 1 then  

        case 
            When @FN_1ST_UPB_Band is NULL then  '    Missing'
            When @FN_1ST_UPB_Band = 0 then '  0'
            When @FN_1ST_UPB_Band < = 50000 then ' <=50K'
            When @FN_1ST_UPB_Band between 50000 and 100000 then ' 50-100'
            When @FN_1ST_UPB_Band between 100000 and 150000 then '100-150'
            When @FN_1ST_UPB_Band between 150000 and 200000 then '150-200'
            When @FN_1ST_UPB_Band between 200000 and 250000 then '200-250'
            When @FN_1ST_UPB_Band between 250000 and 300000 then '250-300'
            When @FN_1ST_UPB_Band between 300000 and 350000 then '300-350'
            When @FN_1ST_UPB_Band between 350000 and 400000 then '350-400'
            When @FN_1ST_UPB_Band between 400000 and 450000 then '400-450'
            When @FN_1ST_UPB_Band between 450000 and 500000 then '450-500'
            When @FN_1ST_UPB_Band > 500000 then '500K+'
            else null End
         else 'Invalid' End

    RETURN @return
End

Answer 1

ISNUMERIC 在 INT 字段上始终为 true，您可以放心地丢弃它。

试试这个：

declare @FN_1ST_UPB_Band INT
SET  @FN_1ST_UPB_Band = -1000

select  case 
            When @FN_1ST_UPB_Band is NULL then  '    Missing'
            When @FN_1ST_UPB_Band < 0 Then 'Invalid'
            When @FN_1ST_UPB_Band = 0 then '  0'
            When @FN_1ST_UPB_Band < = 50000 then ' <=50K'
            When @FN_1ST_UPB_Band between 50000 and 100000 then ' 50-100'
            When @FN_1ST_UPB_Band between 100000 and 150000 then '100-150'
            When @FN_1ST_UPB_Band between 150000 and 200000 then '150-200'
            When @FN_1ST_UPB_Band between 200000 and 250000 then '200-250'
            When @FN_1ST_UPB_Band between 250000 and 300000 then '250-300'
            When @FN_1ST_UPB_Band between 300000 and 350000 then '300-350'
            When @FN_1ST_UPB_Band between 350000 and 400000 then '350-400'
            When @FN_1ST_UPB_Band between 400000 and 450000 then '400-450'
            When @FN_1ST_UPB_Band between 450000 and 500000 then '450-500'
            When @FN_1ST_UPB_Band > 500000 then '500K+'
            else null
        End

Answer 2

在这种情况下（没有双关语）

这样做是

When @FN_1ST_UPB_Band < 0 Then 'Invalid'
When @FN_1ST_UPB_Band >= 0 and @FN_1ST_UPB_Band < = 50000 then ' <=50K'

因为 -1000 小于 50000

Answer 3

因为它们是按顺序求值的，所以 -1000 是 <= 50000，因此它求值为 true 并返回“<= 50000”。您需要 1 到 49999 之间的值（不是 50000，因为您有 50000 在 2 种情况下会评估 true）。

Answer 4

那么-1000不是小于50000吗？您不需要这种情况吗：

当 @FN_1ST_UPB_Band 在 0 和 49999 之间时，则 ' <=50K'