当包含在“WHERE...IN"中时，udf 中出现奇怪的错误；条款 [英] Weird error in udf when included in "WHERE...IN" clause

问题描述

我有一个拆分字符串的函数(为了清楚起见，粘贴在最后).单独使用时，此功能按预期工作.示例:

I have a function that splits up a string (pasted at the end for clarity). This function works as expected when used alone. Example:

SELECT value
FROM dbo.mg_fn_Split('2#1','#')

退货

-- value --
--   2   --
--   1   --
-----------

但是当在WHERE IN"子句中使用时，如本例所示(稍后将详细介绍 tableA):

But when used in a "WHERE IN" clause, as in this example (more on tableA later on):

SELECT * FROM TableA WHERE TableA.id IN
(
  SELECT value
  FROM dbo.mg_fn_Split('2#1','#')
)

我收到错误消息:传递给 LEFT 或 SUBSTRING 函数的长度参数无效."

I get the error: "Invalid length parameter passed to the LEFT or SUBSTRING function."

此处以表A为例.使用不同的表(假设它们有 id 列)有时会返回正确的结果，而在其他表上我会得到错误.

TableA is used here as an example. Using different tables (assuming they have the id column) sometimes returns correct results, while on other tables I get the error.

我假设它与执行顺序有关，但我仍然看不到什么会破坏"函数.

I'm assuming it has something to do with order of execution, but I still fail to see what could "corrupt" the function.

我正在寻找发生了什么"的解释，而不是改用这个".我知道我可以使用连接来获得结果.

I'm looking for a "what's happening" explanation, not a "use this instead". I know I can use joins for example to get the results.

函数定义:

-- Description: Returns a table containing the results of a string-split operation.
-- Params:
--      DelimitedList: The string to split
--      Delimiter: The delimiter char, defaults to ','
-- Columns:
--      Position - The char index of the item
--      Value - The actual item
-- =============================================
CREATE Function [dbo].[mg_fn_Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value <= DataLength(CL.List) / 2
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

我已经设置了一个小提琴来展示这一点:http://sqlfiddle.com/#!3/9f9ff/3

I've set up a fiddle to exhibit this: http://sqlfiddle.com/#!3/9f9ff/3

推荐答案

当你在内部查询中的数据变成如下时会发生这种情况.

This happens when you data in inner query becomes as follows.

选择值FROM dbo.mg_fn_Split('#','#') --------------> 你会在这里得到错误.

SELECT value FROM dbo.mg_fn_Split('#','#') --------------> You will get error here.

选择值FROM dbo.mg_fn_Split('2#1','#') -------------> 这里没有错误.

SELECT value FROM dbo.mg_fn_Split('2#1','#') -------------> No error here.

选择值FROM dbo.mg_fn_Split('2','#') -----> 这里没有错误.

SELECT value FROM dbo.mg_fn_Split('2','#') --------------------> No error here.

选择值FROM dbo.mg_fn_Split('','#') ----------------------> 这里没有错误.

SELECT value FROM dbo.mg_fn_Split('','#') ----------------------> No error here.

所以基本上当您拆分的数据和分隔符相同时，就会发生错误.

so basically when the data you are splitting and the delimiter is same the error will happen.

问题在于这些陈述.

      " Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
        + @DelimitedList
        + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End"

如果你把它改成

    Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '1' End
        + @DelimitedList
        + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '1' End

然后它会很好地工作......你所做的就是添加'1'而不是''......希望这会有所帮助.

then it will work out fine.. all you are doing is adding '1' instead of ''... hope this helps.

这篇关于当包含在“WHERE...IN"中时，udf 中出现奇怪的错误；条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！