T-SQL Case语句在奇怪的特定情况下失败

Question

我遇到了一个涉及自引用临时表、存储过程和视图的问题。

我使用一个存储过程来动态生成sql，它将根据用户在网页上选择的过滤器返回结果。用户可以做的一件事是根据湖泊所在的排水系统搜索湖泊。因此，如果搜索排水系统a，我们必须在搜索中也包括它的所有子节点，因为Lake只知道它最直接的排水系统。

当搜索排水列表时，进程创建以下

WITH DrainageHeirarchy AS
        (
        SELECT d.DrainageID, d.DrainageDesc, d.ParentDrainageID
        FROM Drainage d
        WHERE d.DrainageID IN (161)
        UNION ALL
        SELECT d.DrainageID, d.DrainageDesc, d.ParentDrainageID
        FROM Drainage d
        JOIN DrainageHeirarchy ON d.ParentDrainageID = DrainageHeirarchy.DrainageID
        )

*注意，在这种情况下，161只是一个逗号分隔的排水id列表，用户正在过滤。

现在，我们想从一个有湖泊数据的视图中提取信息。因此，该过程生成以下select语句。

select DISTINCT
    l.Year1stStocked
from dbo.vwlakeextended l
WHERE DrainageID IN (SELECT DrainageID FROM DrainageHeirarchy)

*请注意，为了简单起见，我已经删除了它下拉的许多其他字段，以及where子句中似乎不会引起任何问题的其他检查。

用户选择排水161的情况是特殊的，因为它会导致错误。要理解这一点，我们必须首先了解视图是如何生成的。vakeextended生成该字段。

case when isnumeric(l.Year1stStocked)=0 or l.Year1stStocked is null
   then (select cast(min(s.stockyear) as varchar(255)) from stk.stocking s where replace(SiteCode, ' ', '') = replace(l.lakecode, ' ', ''))
   else case when isnull(               
                            (select min(s.stockyear)
                             from stk.stocking s
                             where replace(SiteCode, ' ', '') = replace(l.lakecode, ' ', ''))
                                     ,year(getdate())
                         ) < l.Year1stStocked
            then (select cast(min(s.stockyear) as varchar(255)) from stk.stocking s where replace(SiteCode, ' ', '') = replace(l.lakecode, ' ', ''))
            else l.Year1stStocked
        end
end as Year1stStocked,

实际上，如果源表中没有给出年份，则从另一个表中生成数字。源表使用varchar(255)来存储年份，如果不知道年份，则使用描述符(不是最优的，但除了点之外)。

比较

我已经检查了isnumeric()的问题，因为它通过数字相关字符(+，-，$等)，但它失败的字符串是'?'，它在数据中，并在isnumeric()

时返回0

为什么它真的让我烦恼

在本例中，temp表返回一个包含三个id的列表:161,162和163。

如果我用那些id替换子查询，一切工作正常。如果我用数字161替换列排水id，它工作得很好。

在任何情况下，视图在转换'?'转换为int，因为它不应该有机会。我不知道为什么在这种情况下，它会。

Answer 1

我想提个建议:

我不确定如何填充描述符但我会解析描述符字段为该行的任何空白字符，如char(11)传递给numeric()时，结果为假阳性:

declare @v varchar(10);
set @v = CHAR(11);
select isnumeric(@v);
select @v;

这将排除任何其他可能不可见的原因。

CHAR(9)， 10,11,12 &13等返回1